Re: Processing queries simultaneously

From: Paul fpvt2 (Paulfpvt2_at_discussions.microsoft.com)
Date: 12/30/04 

Date: Thu, 30 Dec 2004 08:59:03 -0800

Thank you for your reply.

Thanks for the suggestion on the stored procedure. I think I might be able
to simplify
the SP because the user only requests in hours, so I can get rid of the
other conditions.

>Another question is where is the clustered index on the Packet table?
The clustered index is in Packet_contract, Packet_time. I find this
combination
to produce the fastest result.

>You do but not for this expensive query. Add a hint to the query of MAXDOP
>= 1 to these big queries so they only use one processor and leave the other
>for the other users.
If I force the query to use one processor, will it slow down this query ?

>Never do that. You are seriously hurting performance by fetching only a few
>rows at a time, not to mention the amount of extra network traffic. Get all
>the rows at once then do what ever processing you need or do more
>pre-processing in the back end and send fewer rows over to the client.
SQL Server is in the same machine with the VB server application that
request the data.
What I meant was from the VB program, I call the stored procedure to get all
the records. Then when the total number of characters of the data is >= 1000
(c_MAXOUT), I send the data to the client.

This is from my code:
      sSql = "exec mySP " & "@sType='" & sType & "'," & "@sContract='" &
sContract & "'," & "@iNum=" & iNum
      Set rs = New ADODB.Recordset
      Set rs = m_adoCon.Execute(sSql)
      Do While Not rs.EOF
         sStr2 = "|" & rs("PACKET_DATA")
         If Len(sStr2) + Len(sStr) < c_MAXOUT Then
            sStr = sStr & sStr2
         Else
            sStr = Mid(sStr, 2) & "|"
            FromClient.Send sStr
            sStr = sStr2
         End If
         rs.MoveNext
      Loop

Thank you.

"Andrew J. Kelly" wrote:

> >>That basically means you need to have a query and schema that will benefit
> >>from
> >>parallel processing first.
> > Would you please tell me what you meant by this ?
>
> In order for SQL Server to use more than 1 processor to process a request
> from a single user it must be something that can take advantage of parallel
> processing. For instance a if you are issuing a select that will use an
> index to retrieve just 5 rows with a seek there is little chance it will use
> multiple processors for such a simple operation. If you are scanning a
> clustered index on a large table it will most likely try and spawn multiple
> threads to read the data files in parallel.
>
> > CREATE procedure mySP
> > @sType varchar(1),
> > @sContract varchar(8),
> > @iNum smallint
> > as
> > SET NOCOUNT ON
> > if (@sType = 's')
> > select packet_data from packet where packet_time >= dateadd(second,
> > @iNum * -1, getdate()) and packet_contract = @sContract
> > else if (@sType = 'm')
> > select packet_data from packet where packet_time >= dateadd(minute,
> > @iNum * -1, getdate()) and packet_contract = @sContract
> > else if (@sType = 'h')
> > select packet_data from packet where packet_time >= dateadd(hour,
> > @iNum
> > * -1, getdate()) and packet_contract = @sContract
> > else if (@sType = 'd')
> > select packet_data from packet where packet_time >= dateadd(day,@iNum *
> > -1,CONVERT( CHAR(10), getdate(), 101)) and packet_contract = @sContract
> > SET NOCOUNT OFF
>
>
> I have several comments on this sp. First off I would probably create 4
> different sp's and call the appropriate one depending on the sType from the
> front end. This is due to the fact each one may have a different query plan
> (or some share the same ones). For instance there is a good chance that if
> you are only looking for a few seconds worth of data you can use an index in
> a Seek. Where as if you are looking for days worth it will probably scan an
> index or table. The plan may be dictated by the first call to the sp and
> subsaquent plans may use that even though they are improper.
> But in place of that I would use one query and calculate the date at the
> begining.
>
> if (@sType = 's')
> SET @DT = dateadd(second, @iNum * -1, getdate())
> if (@sType = 'm')
> SET @DT = dateadd(minute, @iNum * -1, getdate())
> etc. or use a CASE expression.
>
> Then you only have to list the query once. It makes for a lot more readable
> and maintainable code.
>
> Another question is where is the clustered index on the Packet table?
> Hopefully it is on the Packet_Time column. If not you seriously want to
> consider putting it there.
>
>
> > the 1st user's request to be completed. Because currently if 1st user's
> > request return 500K records, the 2nd user will have to wait until 1st
> > user's
> > request is completed before his request is being processed. I thought for
> > this I want to user more than 1 processor ??
>
> You do but not for this expensive query. Add a hint to the query of MAXDOP
> = 1 to these big queries so they only use one processor and leave the other
> for the other users.
>
>
> >>What is the client using to receive the result set?
> >>Hopefully it is not fetching them one row at a time.
> > I send data to the client a few records at a time (< 1000 characters), so
> > it
> > can be between 4-8 records at a time.
>
> Never do that. You are seriously hurting performance by fetching only a few
> rows at a time, not to mention the amount of extra network traffic. Get all
> the rows at once then do what ever processing you need or do more
> pre-processing in the back end and send fewer rows over to the client.
>
>
>
>
>
> --
> Andrew J. Kelly SQL MVP
>
>
>
>

Relevant Pages

  • Re: sql server slower than MS Access
    ... update query before actually committing the results. ... The clustered index is not on the columns being updated. ... I SET ROWCOUNT in the stored procedure to 100000, the query executes ...
    (microsoft.public.sqlserver.server)
  • Re: Speed question
    ... > Your suggestion to change the clustered index to the packet_time works ... I execute the query from VB6 using ADO. ... When I run the stored procedure, it takes about 1 minutes to ... >>> getdate()) ...
    (microsoft.public.sqlserver.programming)
  • Re: IS NULL on field is not using index placed on that field
    ... > criteria in your WHERE clause the query engine needs to look through 2 ... > NULL" you get a nice quick plan using your nonclustered index too. ... > efficiently by just scanning the whole clustered index until it's got ... > even took out the null values and made the DeliverDate column NOT NULL ...
    (microsoft.public.sqlserver.programming)
  • Re: Whats the deal with PAGEIOLATCH_SH? - PAGEIOLATCHIssue20040426.zip (0/1)
    ... That table has no primary key and no clustered index. ... This table used to have very poorly chosen clustered index, ... SQL Server is certainly capable of handling ... >being missing) is messing up the main query. ...
    (microsoft.public.sqlserver.programming)
  • Re: Leftmost column in an index
    ... That's one example where the optimizer creates these _WA% statistics to ... which is the cost of a table scan. ... But this query: ... >> would likely change to a table scan or clustered index scan. ...
    (microsoft.public.sqlserver.programming)
Quantcast