April 25, 2003 at 8:39 am
hey
I have a sql2000 server SP3 and I migrate a database from SQL7. I run two basically equal select in the same tables on the same database on the same sql2000: 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 difference is that the substring range: 20 to 30. Notice that the limit is not fixed. Sometimes the jump in execution time happened when I change from 90 top 91 one day: the other when I jump from 25 to 26.
really I dont' know. (Fields are 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.
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 between 0 and 5%
1) same times without the option of processing in one CPU only
2) both table are index on the specific fields.
thanks for all the help (really needed)
Edit/delete
April 25, 2003 at 11:37 am
I would suggest you to look into the execution plans on both query. They might be different.
April 25, 2003 at 11:48 am
First, make sure all statistics are updated. Second, try this instead:
select count(*) from uu_resume_ses_dummy_dummy
where not exists
(select * from iis_uu_diario_resume where substring(col018_dominio,1,30)
= substring(uu_resume_ses_dummy_dummy.dominio,1,30))
and see if it makes any difference...
April 28, 2003 at 1:40 am
thanks for the answers
1) the plans are the same
2) sure that changing the statement it works better but the point is. why there is such a big difference in performance just of a character long key? This time I look at it but next?
April 28, 2003 at 4:45 am
When testing make sure each run is done with clean buffers otherwise you are not really in the realm of apples to apples.
Use
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
Or restart the SQL Server Service when testing to get an accurate picture of what each is doing.
Also, look at memory changes, hard drive IO and so on with PerfMon.
April 28, 2003 at 4:53 am
Thanks for the counselling. I will apply it, but....
I made the test many times (5 or 6) one after another one: every of the two SELECT was always same time: the really fast always fast and the really slow always slow, also after many repetition
April 30, 2003 at 10:43 am
rickzan,
I suspect your varchar-column runs across some kind of page-border when you change the substring from 20 to 30 characters. This may make a comparison more difficult for the server when comparing 30 chars, since it may have to reload more data from different locations.
Question 1: Ist this Column a NOT-NULL column?
Question 2: Are ther other varchar columns before your critical character-column and are they NULL-allowed or NOT NULLable ?
As far as I remember, a varchar or a NULLable column make it more expensive for the server to retrieve data. You may want to experiment with making your column the first varchar column of the table and make it NOT-NULLable. I would be interested if this helps.
Kay27
April 30, 2003 at 11:14 am
that are the defintion of the table
I will test your seuggestion as soon as I can
thanks a lot
iis_uu_diario_resume
Hora_iniciovarchar2551
Hora_finevarchar2551
paginasint41
hora_origenchar101
mes_origenchar101
dia_origenchar101
uuvarchar501
col018_dominiovarchar2551
uu_sessionvarchar501
uu_dia_castint41
uu_mes_castint41
all rows allow nulls
1UUvarchar501
0UUNumdecimal91
0UU_Sessionvarchar2551
0UU_SessionNumIdint40
0UU_SessionNumint41
0Dominiovarchar2551
0Dominio_generalvarchar501
0fechadatetime81
0Hora_iniciodatetime81
0Hora_finedatetime81
0Duracionint41
0Paginasint41
0RangoPaginasint41
0Hora_origenint41
0Dia_origenint41
0Mes_origenint41
0Caracteristicachar101
0Cardinalidadint41
all colums allow nulls except uu_sessionumid
April 30, 2003 at 11:37 am
try this:
select count(*) from uu_resume_ses_dummy_dummy
where substring(dominio,1,30)
not in (select substring(col018_dominio,1,20)+substring(col018_dominio,20,10)
from iis_uu_diario_resume where substring(col018_dominio,1,20)+substring(col018_dominio,20,10)
= substring(uu_resume_ses_dummy_dummy.dominio,1,20)+substring(uu_resume_ses_dummy_dummy.dominio,20,10))
option (maxdop 1)
April 30, 2003 at 11:38 am
actually I made a small mistake there :
select count(*) from uu_resume_ses_dummy_dummy
where exists (select substring(col018_dominio,1,20)+substring(col018_dominio,20,10)
from iis_uu_diario_resume where substring(col018_dominio,1,20)+substring(col018_dominio,20,10)
= substring(uu_resume_ses_dummy_dummy.dominio,1,20)+substring(uu_resume_ses_dummy_dummy.dominio,20,10))
option (maxdop 1)
May 1, 2003 at 5:32 pm
Just a thought: is it possible the query using the substing(30) might cross a line from using RAM to using HDD for storage? I can't really see how that would account for an extra 57 minutes and 58 seconds....
Rick Todd
May 2, 2003 at 12:41 am
quote:
is it possible the query using the substing(30) might cross a line from using RAM to using HDD for storage?
I had the same suspicion. A statistics on I/O with the number of logical and physical reads for either select-statement should clear this up. Rickzan, can you get these numbers ?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply