Performance loss when upgrading v7 to 2000

[Giddygoat]

I have some queries that look something like this.

SELECT a, b, c, d.....
FROM table1, table2
WHERE a = 1
AND b in (SELECT x
FROM table3
WHERE x = i)
OR b in (SELECT y
FROM table4
WHERE y = j
AND k in (SELECT x
FROM table5
WHERE z = c))

In version 7 these queries where executing sub 1 second in SQL Server 2000 they are taking 30 seconds +.
Any ideas?

John

 

[osjohnm]

Hi

Have you run sp_updatestats on the database?

SQL 2000 and SQL 7 handle indexes differently. By moving the database from 7 to 2000 the stats on the indexes have become out dated.

Microsoft themselves recommend that you run sp_updatestats on a SQL 2000 database that has come from SQL 7 to make sure all the indexes are up to date.

Hope this helps

John

 

[tlbroadbent]

Have you analyzed the query execution plan to see where the slowdown occurs? Are statistics up to date on the database? Do you really want to JOIN table1 and table2 with no JOIN criteria producing a CROSS JOIN?

It's hard to tell from the code you posted whether the queries can be improved or not? Have you considered using INNER JOINs or WHERE EXISTS rather than WHERE IN queries. Depending on table sizes and other factors the resulting query could be significantly faster.

SELECT a, b, c, d.....
FROM table1, table2
WHERE a = 1
AND EXISTS (SELECT *
FROM table3
WHERE x = i
AND x = table?.b)
OR EXISTS (SELECT *
FROM table4
WHERE y = j
AND y = table?.b
AND EXISTS (SELECT *
FROM table5
WHERE z = c
AND x = table4.k)) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[Giddygoat]

tlbroadbent, our right I didnt mean to put the cross join in, I can't use INNER JOINS and EXISTS because the SQL must also work in ORACLE. I have run sp_updatestats and it did make a difference.

I did change the "OR b in (SELECT b" on line 7 of the origanal query with "UNION SELECT b", this is correct in the context of what I'm trying to achieve. This caused a massive improvement in performance. Any ideas why this would be the case?