Slow SQL Query

  • 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

  • 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]

  • 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
  • but i m running this SQL query on 4 GB ram machine

  • 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??

  • 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
  • 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

  • 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]

  • hey thanx ..

    let me check..

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

  • 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
  • but i heard that we should not use the indexes columns in where clause

    when we update that particular table.

  • 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
  • thanx for your help....

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

  • 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