Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    20

    Unanswered: SQL Trace Question

    I ran a trace on my SQL Server and found that a single SELECT statement showed up the same number of times as the number of id numbers I was selecting on. In the hopes of optimizing my program, it ran worse

    Instead of having:

    SELECT * FROM table_name WHERE id='1'
    SELECT * FROM table_name WHERE id='2'
    SELECT * FROM table_name WHERE id='3'
    ...
    SELECT * FROM table_name WHERE id='250'

    for all the rows i need data from (sometimes up to 250 rows). I changed the select statement to:

    SELECT * FROM table_name WHERE id='1' OR id='2' OR id='3' .. id='250'

    When i run a SQL Trace if i have 250 id='num's in my SELECT, the same query shows up 250 times in the trace with high CPU and READS. Can Anyone explain and help me get the optimization Im looking for?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    are you worried about the number of SELECT statements in your trace or the performance of the query?

    Have you applied an index to [id]? Is [id] unique? Is it the primary key? Is there a clustered index defined on the table already?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Are you running this query through a VB script or application of some sort, or through Query Analyzer?

  4. #4
    Join Date
    Sep 2004
    Posts
    20
    Thrasymachus & MCrowley:
    The id is unique and is a clustered index. I am definitly worried about the number of SELECT statements.

    I wrote a sort of inbox (like an email inbox) for a fax server using VB6. Every 5 seconds i refresh a MSHFlexgrid that holds new faxes. Instead of getting new data for each fax in the inbox one row at a time, I want to get data for all faxes in my inbox with one query instead of number of queries=number of faxes. If my inbox has 250 new faxes and 10 people are using the inbox program at once, thats 2500 SELECT statements every 5 seconds if I go one row at a time. You can see why I wanted to make a refresh one query.

    Im totally baffled! HELP!

  5. #5
    Join Date
    Sep 2004
    Posts
    20
    Can Anyone else mkae sense of my problem?

Posting Permissions

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