Link to home
Start Free TrialLog in
Avatar of msnyder8
msnyder8

asked on

SQL query very slow in Sybase but not in SQL Server

The following query runs at least 30 minutes in Sybase Adaptive Server Anywhere 9 (got tired of waiting), but only takes a few seconds in SQL Server 2000.

SELECT n.names_id, n.first_name, n.last_long_name, n."address", n.address_2, n.company
FROM names n WHERE EXISTS (SELECT 1 FROM names n2
WHERE IsNull(n.last_long_name,'') = IsNull(n2.last_long_name,'')
AND IsNull(n.first_name,'') = IsNull(n2.first_name,'')
AND n.names_id <> n2.names_id)
ORDER BY n.last_long_name, n.first_name

I am querying a table of names with 162,000 records.  I have rewritten the query using a join instead of the subquery, but either way it takes forever.

Any help is appreciated.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

do you have the same indexes on the sybase database side?
Avatar of msnyder8
msnyder8

ASKER

The sysbase side has 4 indexes, but only 2 on columns I am querying (last_long_name and first_name).  The SQL Server side has none.
then possibly sybase is trying to use the indexes which actually slows down your query.
can you get the execution plans from the 2 executions?
been running it in Sybase for about 2 hours with no response (and no execution plan).  Execution plan in SQL Server is graphical:

In Parallel: 2 table scans (15% each), 2 compute scalar (0%), 2 parallelism/compute streams (6% and 12%)

Then:  hash match/right semi join (19%), sort(25%), parallelism/Gather streams(7%), select (0%)

Should I just drop the indexes in Sybase and then rebuild them later?
I dropped all 4 indexes in Sybase and still have the same problem.  Not getting an execution plan in Sybase.  I assume it gets displayed once the query completes.
If I remove the IsNull function, the problem is solved.  Don't know why the IsNull has such an affect on performance or why it would seem to affect Sybase and not SQL Server?
I would have expected the isNull function to have the same bad effect on the sql server side, but apparently...
ASKER CERTIFIED SOLUTION
Avatar of Computer101
Computer101
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial