really strange performance problem

hey
I have a sql2000 server SP3 and I mgrate a databse from SQL7. I run teo basically equal select: he first one 2 seconds the second one 58 minutes.....

select count(*) from uu_resume_ses_dummy_dummy
where substring(dominio,1,20)
not in (select substring(col018_dominio,1,20)
from iis_uu_diario_resume where substring(col018_dominio,1,20)
= substring(uu_resume_ses_dummy_dummy.dominio,1,20))
option (maxdop 1)

select count(*) from uu_resume_ses_dummy_dummy
where substring(dominio,1,30)
not in (select substring(col018_dominio,1,30)
from iis_uu_diario_resume where substring(col018_dominio,1,30)
= substring(uu_resume_ses_dummy_dummy.dominio,1,30))
option (maxdop 1)

the only differencei s that the substring range: 20 to 30. Notice that the limit is not fixed. SOmetimes the jump in execution time happende when I change from 90 top 91......
really I dont' know. (Fields ara varchar(90) but it was the same with varchar(255). the PLAN are exactly the same. in the second case the CPU was 50% fror 58 minutes fixed.
thanks for all the help (really needed)essentialy, for every record in the "uu_resume_ses_dummy_dummy" table you are looking at every record in the "iis_uu_diario_resume" table Using only one processor.

Since you will be looking at every record you have the potential of being delayed by locks, index leaf splits and other traffic. What happens if you run these selects on a quiet system. I suspect the time diffrence is small.|||I was runnng these queries both in a "busy" server (4 cpu, 4Gb RAM) and on a really quiet server (2 CPU, 4GB RAM) with same timing. Quiet server means that basically % of CPU without that select was netween 0 and 5%|||forgot things.

1) same times without the option of processing in one CPU only
2) both table are index on the specific fields.

what you say is ok. problem is:why almost identical queries have such a big big big difference in execution time?

Real Time Data

I currently have an application which retrieves stats from a SQL database that are updated throughout the day. My application updates every 3 seconds to retrieve these stats but I found that it's quite expensive to the server's CPU when there are several clients running my application. Are there any other methods for extracting the data that won't require so much CPU. The fact that the database is being hit so much isn't good either. I have already written a webservice hoping that this would improve things but I'm not sure it has.

Hi,

is it really necessary to have real time data? Actually I mean, does the data gets updated every 3 seconds or less? If not you could, in your webservice, make use of the Cache object and have, you need SQL 2005 for this I believe, have SqlCacheDependency so that only when something in a table changes only then the Cache gets updated.

Grz, Kris.

|||The data are phone stats which are used to show calls received in an application.The application shows desks in a plan and is updated when a phone is ringing etc... This is why it needs to be in real time but I can't think of any better way to do this. The call events are stored in a table called CallRecords but I also have a snapshot table which the application uses. It selects all from this table which has the desk number, answered, missed and total calls columns which are incremented based on each event that comes through to CallRecords.|||

Hi sichiu,

The accuracy of data and performance of the server are always a pair of conflicts.

You can for better performance, you can cache data and set an expire duration. However, that will cause data not being real time.

In this case, setting the expiration duration is very important.

Another way is that you can cache data locally, and use SqlDependency class to notify your app when the data is changed through SQL notification service. For more information, please check

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx

Real Impact of MS Performance Counters

; have been collecting information about 20 performance counters (memory, IO,
cpu, SQL) that refresh every 15 seconds; would that have any performance hit
in the server? what are best practices when collecting information via
performance counters?
ThanksCollecting these counter values at a 15-second interval should have
absolutely no performance impact. (If on the other hand for however small
chance it does have an impact, your machine is greatly under-spec'ed). No, I
wouldn't worry about any performance impact at all.
Linchi
"Carlos" wrote:
> ; have been collecting information about 20 performance counters (memory, IO,
> cpu, SQL) that refresh every 15 seconds; would that have any performance hit
> in the server? what are best practices when collecting information via
> performance counters?
> Thanks