SQL Server Performance Forum – Threads Archive

SQL 7 taking forever to "parse and compile" query

Hi, I have a query on a SQL 7 server that contains an inner join, and about 5 left outer joins on 3 separate 50,000-record tables, and 4 smaller 10-to-30 row tables. While the indexes make it so the query runs pretty fast (about 2000 reads total and 2 secs), my main problem is the "Parse and compile time" which runs at about 11 seconds on the server. I have tried copying the tables and its indexes to a SQL 2000 server on another machine and the parse and compile time goes down to 1 sec (on a machine that’s about 6 times slower) !!! Have any of you encountered that with SQL 7 ? Could it be a server configuration problem and not the SQL engine itself ? I can’t really make a stored procedure as to keep the number of joins down, I build the query directly depending on what the user is searching for. I tried reducing the number of joins even more, but then I have to make so many little queries to get the data that it takes the engine just as much time to display the results. Don’t really have the option to upgrade the server to SQL 2000 either…. so any help will be appreciated !!! Have tried SQL 7’s profiler and it gave me an error with one of the queries in my trace (I’ll try and make another trace tomorrow to see)… I updated the statistics and tried to force the indexes I know need to be used but it didn’t help…. Thanks !

This is strange indeed. Is the execution plan the same on both 7.0 and 2000, or are they different? —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

Apart from SQL 7 showing the "Parallelism/Repartition Streams" everywhere (SQL 2000 doesn’t show that), there is one difference where SQL 7 uses a "Hash Match/Right Outer Join" while SQL 2000 uses a "Merge Join/Right Outer Join". Everything else is similar, mainly hash matches…

I do know that the Query Optimizer was improved in 2000. Try running the query in 7.0 using the query hint OPTION (MAXDOP 1)

wuhoo ! Added a Hash query hint on one of the tables and I’m already down to 800ms (from 11 secs). Funny enough, I added the Hash on the only inner join in the middle of the other left outer joins… So I’m wondering if SQL 7 has more trouble choosing when mixing inners and outers together… Anyway, thanks a lot for the hint; I had tried to force the indexes and that hadn’t helped, but now it seems I’m going to take this query time down to nothing… [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

I have an indirect problem that is caused by what I did above which I may just know the function for but I’m not sure… When I didn’t force the hashes, it would do a hash match/right outer join. Now that I have forced hashes, it does left hashes. For some reason this takes an extra 3-4 seconds on some of the queries. While I can edit the SQL depending on the query, I am wondering if/how you can force hash matches to merge right instead of left without having to transform the whole query to run right outer joins instead of left outers. If I do need to change the query completely I will but I’d rather avoid it if there’s a simple way ! Thanks for your responses, they helped a lot [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Julien Desmottes

Do you specify the table owner in the SQL statement? IIRC, the optimizer will only work properly if it has a qualified object name; and that could be less of a problem in SQL 2000. ………..
FROM dbo.MyTable1 AS T1
INNER JOIN dbo.MyTable2 AS T2
ON T1.Field = T2.Field
……….. etc., etc.

Hi, I wasn’t doing that, however when I tried it it didn’t help at all — same parsing and CPU times… I included it in my code anyway if it may be a problem with SQL 7. Thanks for the tip.

Actually that problem is less of one now. It turns out that server was being pushed to its limits by 2 reporting programs running and obviously stuck doing big SQL jobs. Killing the programs solved my response times problems. However, I tested and it did not solve the parse and compile times problem I was having before I forced hashes. They were exactly the same as before (about 11 seconds). Julien Desmottes

]]>