Re: performance of 2 queries

From: Mark J. McGinty (mmcginty_at_spamfromyou.com)
Date: 10/29/04 

Date: Fri, 29 Oct 2004 16:05:38 -0700

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:%23a66%23AQvEHA.2536@TK2MSFTNGP11.phx.gbl...
> Looping through an entire resultset and testing each value is by far the
> least efficient solution.

I agree... and trimming the field value every time, yikes! If the actual
data can't be groomed for some reason, caching a copy and grooming that
would help, but even so this is a poor solution.

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

That depends on which way this app will need to scale. If the number of
records to search is expected to grow substantially, then yes I agree,
stored procedure, good indexes, etc. A db server will be much more
efficient with a large number of records.

However, 3K is not really a large number of records, even if each row
consummed 300 bytes (which would be massive overkill) the set wouldn't even
use 1MB. Even if the record count grew to 10K-15K rows (given that it
didn't change often) it'd still be very much practical to cache the whole
set and perform searches on the client -- using Recordset.Find on an
optimized field, rather than an exhaustive search of course. You'd still
get sub-second results even on modest client PCs and without a round-trip to
the server for every lookup.

This sort of distributed paradigm would scale almost infinitely, with no
performance degradation across tens of thousands of clients (except when
refreshing the set, and there are a number of ways that could be mitigated)
whereas with server-based lookups, best-case scenario is that performance
will degrade on a linear scale as the number of clients grows. (Concurrency
issues could easily cause non-linear degradation too, and that's never a
good thing.)

So in short I have to disagree in principle that a best solution can be
determined from what's been presented; the nature of the data, number of
users and growth projections would all need to be considered in order to
definitively say which is best. There are most certainly some real-life
scenarios under which a stored procedure solution *would* be the best, but
just a certainly, others under which it *wouldn't* be the best by a long
shot.

-Mark

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