Re: Very slow access using MDAC2.8
From: Mark McGinty (mmcginty_at_spamfromyou.com)
Date: 03/10/05
- Next message: Stephen Howe: "Re: SET SHOWPLAN_ALL ON in ADO"
- Previous message: Fred: "ADO RecordCount with OLE DB Provider"
- In reply to: Adam: "Re: Very slow access using MDAC2.8"
- Next in thread: Val Mazur \(MVP\): "Re: Very slow access using MDAC2.8"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 10 Mar 2005 06:21:05 -0800
If the performance problem is visible in QA then it likely isn't an ADO
version issue...
Would it be possible to locate the stored proc on the machines with the
data, then call that proc with a 4-part name?
What you want to look at on the machines that are slow is network
utilization while the query is running: how many bytes are going across the
wire? What I suspect is happening is that the calling server is trying to
resolve the query itself, and thus must read a large number of rows from the
box on which the data is stored.
If there is a high degree of network load, and you *really* wanted to see
just exactly what in hell is going on, install Ethereal and "sniff" the
network traffic.
When you get down to it, the bottleneck has to be one of 2 things: you're
either waiting on the other server to return results, or you're waiting on
i/o to transfer the results. In a perfect world, we could rule-out the
latter out of hand, but I've seen some pretty insane behavior in joins
across distributed servers, so it's worth a quick look anyway.
-Mark
"Adam" <Adam@discussions.microsoft.com> wrote in message
news:6B05EF51-155E-4DAD-B34C-4755432AFF4A@microsoft.com...
> Stephen,
>
> It's being called from a C# web service. However, the performance issue
> does
> not seem to depend on this, as the same problem occurs when it's called
> from
> Query analyser. For example, here is the code out of the SP. The print
> getdate(); statements are just used to time the execution. A trace shows
> that
> the main delay is with the select IMEI statement:
>
> print getdate();
> go
>
> DECLARE @IMEI CHAR(15)
> DECLARE @RetVal BIT
> set @imei = '352493001276725'
>
> if not exists (select IMEI from [DB-S-00-01].MAIN.dbo.PHONE where IMEI =
> @IMEI)
> if not exists (select IMEI from [DB-S-00-01].ARCHIVE.dbo.PHONE where IMEI
> = @IMEI)
> SELECT @RetVal = 0
> ELSE SELECT @RetVal = 1
> ELSE SELECT @RetVal = 1
>
> print @RetVal
>
> go
> print getdate();
>
> Regards
>
> Adam
> "Stephen Howe" wrote:
>
>> > It checks to see if a value exists in one of 2 databases on the server,
>> and
>> > returns 1 if it does, 0 if not. Here's the code:
>> >
>> > CREATE PROCEDURE spCheckIMEIExists
>> > @IMEI CHAR(15),
>> > @RetVal BIT OUTPUT
>> > AS
>> > if not exists (select IMEI from [DB-S-00-01].MAIN.dbo.PHONE where
>> > IMEI =
>> > @IMEI)
>> > if not exists (select IMEI from [DB-S-00-01].ARCHIVE.dbo.PHONE
>> > where
>> > IMEI = @IMEI)
>> > SELECT @RetVal = 0
>> > ELSE SELECT @RetVal = 1
>> > ELSE SELECT @RetVal = 1
>> > GO
>>
>> Pretty good SP code. How is it being called from the ADO end?
>> I would like to see the setting up of whatever ADO object is calling
>> this.
>> Thanks
>>
>> Stephen Howe
>>
>>
>>
>>
- Next message: Stephen Howe: "Re: SET SHOWPLAN_ALL ON in ADO"
- Previous message: Fred: "ADO RecordCount with OLE DB Provider"
- In reply to: Adam: "Re: Very slow access using MDAC2.8"
- Next in thread: Val Mazur \(MVP\): "Re: Very slow access using MDAC2.8"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
