Re: performance of 2 queries

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 10/28/04 

Date: Thu, 28 Oct 2004 11:08:25 -0400

Looping through an entire resultset and testing each value is by far the
least efficient solution.

The best solution, in my opinion, is creating a stored procedure and passing
the search criteria to the procedure. 

-- 
http://www.aspfaq.com/
(Reverse address to reply.)
"David" <Obsession_2001@hot-NOSPAM-mail.com> wrote in message
news:418108aa$0$25839$ba620e4c@news.skynet.be...
> Hi,
>
> I have a question about the performance of 2 queries (used in VB6
> [DataEnvironment] application / CTI integration)
>
> When a call arrives I look up the phonenumber in an sql server 2000 SP3
> database table with about 1500-3000 records (3 fields:
> PrimKey,CompanyName,Tel)
> This has to happen as fast as possible ofcourse. I have 2 solutions but I
> don't know which would be the most efficient.
> I've looked at the profiler utility and from what I can tell the first one
> uses a disconnected recordset because I don't see anything happen in
> profiler. However from what I can see in my code I never created this
> recordset disconnected. I don't even know exactly how I would create such
a
> recordset.
> The 2nd solution does generate some activity in profiler, sometimes alot,
> depending on how many clients are using the application (max 10
> conncurrently)
>
>
> Solution #1
>     Dim szCallName As String
>     Data1.rssqlLookupCompanyWithTel.MoveFirst
>     Do While Not Data1.rssqlLookupCompanyWithTel.EOF
>        If Trim(Data1.rssqlLookupCompanyWithTel("tel")) = CallerID Then
> szCallName = Data1.rssqlLookupCompanyWithTel("CompanyName")
>        Data1.rssqlLookupCompanyWithTel.MoveNext
>     Loop
>
> Solution #2
>     Dim szCallName As String
>     Data1.rssqlLookupCompanyWithTel.Close
>     Data1.rssqlLookupCompanyWithTel.Open "SELECT CompanyName FROM
> AllTelephoneNumbers WHERE Tel = '" & CallerID & "'"
>     If Data1.rssqlLookupCompanyWithTel.EOF <> True Then szCallName =
> Trim(Data1.rssqlLookupCompanyWithTel("CompanyName"))
>
>
> Which solution do you think is the best? Or is there another better
> solution?
> In the VB6 dataenvironment I can choose between the following: Client or
> Server side cursor. Which one do I use in this case? The LockType is
> readonly. Cache Size=100
>
>
> thank you,
>
>
> David
>
>

Relevant Pages

  • Re: performance of 2 queries
    ... least efficient solution. ... The best solution, in my opinion, is creating a stored procedure and passing ... > recordset disconnected. ... Dim szCallName As String ...
    (microsoft.public.vb.database)
  • Re: performance of 2 queries
    ... least efficient solution. ... The best solution, in my opinion, is creating a stored procedure and passing ... > recordset disconnected. ... Dim szCallName As String ...
    (microsoft.public.sqlserver.programming)
  • Re: using Command to set Parameters and Recordset to retrive the Query
    ... doesn't the rsData will be interpretate as an input parameter in the SP? ... >> Query and retrive the Recordset so I can use the Paging property ... > Even if this technique of using the parameters in the ORDER BY does work for> you, I suspect that this will defeat your objective of preventing sql> injection. ... See below for a more efficient solution> using a stored procedure. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Not Getting results from Stored procedure
    ... So, running a stored procedure from VB6 can be done fairly easily and while there are fewer whitepapers on how to best do so, I thought my website might still have a few but there are not that many left--the problem is, Microsoft is pulling old unsupported content from MSDN so a lot of those links are gone. ... In any event, when calling a stored procedure, you need to include phrase SET NOCOUNT ON, so that the 'records affected' message that occur from results of Stored proc processing aren't returned to the calling application. ... These messages appear to be returned to the calling application as a recordset. ... when calling a stored proc you need ignore the command object and use a syntactically correct string as the source parameter for the recordset's 'Open' method. ...
    (microsoft.public.vb.database)
  • Re: vbscript logon script getting return data from sql sp
    ... Loop ... > One way to retrieve values from a stored procedure is with a Recordset ...
    (microsoft.public.scripting.vbscript)
Loading