Post reply

Slow SQL Query

  • July 13, 2009 at 8:14 am

    #1023901

    select count(*) from AuditData

    Output:14661374

    select count(*) from AuditData where TATCallType = 'null'

    Output:14661374

    select count(*) from AuditData where Calltolen=12

    Output:14569138

    select count(*) from AuditData where CallTo like '91%'

    output:14566305

    i have index on

    ID-clustered index

    Calltolen-non-clustered index

  • July 13, 2009 at 8:19 am

    #1023906

    Since you are returning ALL the Records , indexing cannot help.

    SqlServer is executing the query as fast as it can, Its now going to be a hardware issue if you need it to run faster.

    So , are you confident enough to start talking RAID arrays etc?


    Clear Sky SQL
    My Blog[/url]

  • July 13, 2009 at 8:23 am

    #1023909

    are you sure this is what you want?

    select count(*) from AuditData where TATCallType = 'null'

    And not

    select count(*) from AuditData where TATCallType is null

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • July 13, 2009 at 8:24 am

    #1023911

    but i m running this SQL query on 4 GB ram machine

  • July 13, 2009 at 8:29 am

    #1023917

    i thought

    Select count(*) from auditdata where tatcalltype ='null'

    is faster than

    Select count(*) from auditdata where tatcalltype is null

    thats why i updated 'null' inseted of NULL

    what u think which one is fast??

  • July 13, 2009 at 8:31 am

    #1023919

    Well they mena completely different things

    what values are stored in that column? Do you store nulls or do you store the text 'null'?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • July 13, 2009 at 8:35 am

    #1023920

    i stores tatcalltype is null

    for testing i update dated 'null ' inseted of NULL

    one question is

    which one is faster?????????

    select * into

    or insert into

  • July 13, 2009 at 9:24 am

    #1023962

    one question is

    which one is faster?????????

    select * into

    or insert into

    Select into will be faster if 'select into/bulkcopy' is set in sp_dbOption as it will be a non-logged operation.


    Clear Sky SQL
    My Blog[/url]

  • July 13, 2009 at 9:33 am

    #1023968

    hey thanx ..

    let me check..

  • July 13, 2009 at 9:40 am

    #1023980

    can i put the index on the columns whos data type is varchar...

  • July 13, 2009 at 9:41 am

    #1023983

    yes you can

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • July 13, 2009 at 9:50 am

    #1023997

    but i heard that we should not use the indexes columns in where clause

    when we update that particular table.

  • July 13, 2009 at 9:56 am

    #1024005

    where did you hear that from?

    It is true that you can hinder the perform of an update by having to many indexes on a table as you will be updating the Index as well as the table.

    But it's a trade off at the end of the date. Is you table used more for updating or selecting?

    Then decide which needs to be faster, and which is currently slower.

    Also try and keeps you indexes small.

    I would also recommend just trying a few things out. Try it with an index and then delete the index and try it again.

    Most of the time, a lot of us here learn these solutions simply by testing and testing again and then testing some more, until we have a solution that works.

    so give it a try and compare the times...

    Hope that helps

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • July 13, 2009 at 10:00 am

    #1024011

    thanx for your help....

    i will definately test what u said.....

  • July 13, 2009 at 10:08 am

    #1024018

    if i want to send you my table defination ..then how can i send that..because the database is large...

Viewing 15 posts - 31 through 45 (of 49 total)

You must be logged in to reply to this topic. Login to reply