SQL Anywhere to SQL Server

1

Hi
It’s probably not a common thing but does anyone have experience
converting an application from working with SQL Anywhere to SQL
Server?
I have a browse which I have not modified except that the dictionary
table now looks at a view on SQL server rather than a Sybase table.
It now takes at least four times longer to load.
Does anyone have any thoughts on this?

2

Really need more info to help.
I work on an application that supports both SQL Anywhere and SQL Server back-ends.
In just about all cases, SQL Server performs better.
What is your view structure, filters and sorting? What are your indexes are on the tables in SQL?

3

Hi Rick
The browse reads from view which obviously reads from a table. The table has several indexes. The browse is using a key of Date,Time,EntityID,RecordID. A matching index exists against the SQL table. The browse displays values from the view and also some local variables which it calculates on the fly using values from the view. There are also a couple of sql functions that are called when you change the selected line in the browse. It’s the initial population of the browse which seems to take a ridiculously long time.

4

Comment out the function calls to see if that is the slow down.
It would still help to post your table structure, sort order, filters, number of rows being returned, etc.

5

Definitions.txa (15.6 KB)

6

Hi Rick

I’ve attached a file which contains the dictionary table definition, sql table definition, sql view definition and the Prop:Sqlfilter text.
I would appreciate any suggestions.

7

Hi Marc,

You are showing Clarion Keys but I don’t see any SQL indexes other than the primary key/clustered index.
You are doing a number of performance killers in your filter. The OR clauses and the CharIndex clauses don’t help.
How many rows are in these tables? Capture a sample statement you can run in mgt studio and capture the actual execution plan.
If so, paste the plan xml into https://www.brentozar.com/pastetheplan/ and send me the link if you’d like me to look at it.

8

Thanks for your help Rick. You pointed me in the right direction and together with a DBA I managed to speed this up some.

1 Like