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.
Databases

Avatar of undefined
Last Comment
Computer101

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

do you have the same indexes on the sybase database side?
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.
Guy Hengel [angelIII / a3]

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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
msnyder8

ASKER
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?
msnyder8

ASKER
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.
msnyder8

ASKER
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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Guy Hengel [angelIII / a3]

I would have expected the isNull function to have the same bad effect on the sql server side, but apparently...
ASKER CERTIFIED SOLUTION
Computer101

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question