February 27, 2006 at 2:04 pm
Long time lurker - but I've been dragged into SQL again...
I've got a linked server on SQL 2000 and SQL 2005 that connects to a Sybase database. Queries are dog *slow* - we're talking minutes for simple SELECT statements.
The kicker?
The same queries run very quickly on Access 2000 using linked tables or using Crystal Reports (using the same DSN.)
Background:
1 ]I set up the DSN using Sybase Open client and dsedit. All pings are timely. Both SQL and Access and Crystal are using the same DSN.
2] The SQL linked server is using "MS OLEDB Provider for ODBC Drivers" and the DSN I created.
3] I'm only allowed to query views on Sybase.
I think the latter may be the kicker because I suspect SQL is trying to do a table scan and isn't "seeing" indexes for some reason, however I'm not sure. I'm not sure why Access is working so well though...
Details:
I'm tasked with pulling data off a Nortel phone switch/ACD. The switch writes data to Sybase. The reporting engines we have (Symposium) use Crystal Reports for the canned reporting over an ODBC connection which seems to run fine.
Due to the nature of the data and system only views are available via any ODBC connection . Connecting to system tables is not possible and if we found a way would void all sorts of agreements.
Any ideas would be appreciated!
March 2, 2006 at 1:11 pm
You might try using pass through queries using OPENQUERY or OPENROWSET and see if that clears things up.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
March 2, 2006 at 1:33 pm
Thanks for the reply!
Tried both OpenQuery and OpenRowSet in SQL 200x and the net result is the same - the queries run slowly in SQL but run quickly in Crystal Reports and Access.
Any other thoughts will be appreciated (thanks to the mod for the bump 🙂 )
March 2, 2006 at 1:37 pm
1. Might be worthwhile to try out the "collation compatible" option for this linked server on the MSSQL side
2. Better to use the OLEDB provider (from Sybase, part of ther 12.5.2 PC client) which uses wire-protocol driver.
3. There is a nice article on sybase's website for Linked Server to MSSQL, which has some options to turn on.
http://www.sybase.com/content/1029118/1029118.doc
isanthumayor
March 2, 2006 at 1:38 pm
That would tend to indicate that the queries being run by Crystal et al are not the same as the query you are running through SQL. Are you sure they are the same?
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
March 2, 2006 at 5:15 pm
Identical.
The queries can be a simple as:
SELECT * FROM FOO WHERE TimeStamp > '3/2/2006'
Run this in SQL ---> 10 minutes later get the data.
Run this in Access [using # instead of ' for the date field]---> 2 seconds get the data.
Run this in Crystal ----> 2 seconds later get the data.
Oddly enough it doesn't seem to matter whether I'm returning 10 rows or 1000 - i n SQL it still takes 8-10 minutes. Tried this on multiple servers...leads me to believe a table scan is occuring before the records are obtained...
March 2, 2006 at 5:19 pm
Thanks for some ideas. I've downloaded the doc and will read up on this.
I'll dig and see if I can obtain the Sybase OLEDB provider....I'm not sure how much of the client we have available to us (if any).
Thanks!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply