Interesting SQL...

Anyone else experience this?

A developer just finished complaining about the performance of one of our databases. Well, he sent me the query and I couldn't understand why it was such a dog. Anyways I rewrote it. The execution plan is totally different between the two. I had no idea specifying the join made such a difference. First sql executed in 7 minutes that 2nd took 1 second.

SELECT
dbo.contract_co.producer_num_id, contract_co_status
FROM
dbo.contract_co,
dbo.v_contract_co_status
WHERE ( dbo.v_contract_co_status.contract_co_id = dbo.contract_co.contract_co_id )
AND contract_co_status = 'Pending'
OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )

SELECT
dbo.contract_co.producer_num_id, contract_co_status
FROM dbo.contract_co
INNER JOIN dbo.v_contract_co_status
ON dbo.contract_co.contract_co_id = dbo.v_contract_co_status.contract_co_id
WHERE contract_co_status = 'Pending'
OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )The two queries look like they will give different results, too. The first one appears to include a cartesian join. The OR in the where clause makes all the difference.|||Don't want to sound like a snob, but it's all due to the order of processing by QP:

1. JOIN
2. GROUP
3. WHERE
4. HAVING

By rewriting the old query you filtered out what the first query had to deal with while still trying to JOIN.|||actually, i believe it's

1. JOIN
2. WHERE
3. GROUP
4. HAVING

Intel Xeon Processor Vs. INTEL Pentium III

Hi ,
I would like to know if some have experience with upgrade INTEL CPU on SQL 2000 DB server .

I have SQL 2000 2* 1233 INTEL Pentium III CPU with 4 GB RAM .

The server main task is log processing from temporary table to Fact table.

I would like to upgrade the CPU to Intel Xeon Processor 1MB cache ~2.4 GHz .

Intel report on ~ 100 performance improvement.

Can someone let me know from his CPU experience what should I expect?It depends. You will have to run some performance monitors on your current box, to see if it is getting bogged down by a lack of CPU. If it is, then yes, the faster CPU could help. If you are more bogged down by disk access, you will see very little performance increase with the new CPUs.

For example, suppose a process takes 10ms of cpu time, and 100ms of disk I/O time. The whole process takes 110 ms. If you get a processor that runs twice as fast, you will end up with a process that runs in 105 ms. Just under a 5% increase in performance.

In general, if your CPU is staying up around 70 - 80%, I would go with the CPU upgrade. If it is consistently less than that, I would not bother.|||Thanks !,
Thank , I have peeks of ~ 20 minute each hour of ~ 80-100% CPU but , I am afraid that it is caused by the SQL server processing (lock management locks for example or other ) so any better CPU will not help ??