Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    3

    Unhappy Unanswered: 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)

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Apr 2003
    Posts
    3
    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%

  4. #4
    Join Date
    Apr 2003
    Posts
    3
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •