asked on

Are my speed issues with remote MSSQL SELECT query (via php) caused by the connection or the server?

Greetings,

I have a situation with a client (client A) where a SQL SELECT query is taking over 3 minutes. Here is the query -

WITH MyProducts AS (SELECT ProductID, cast(GUIDProduct as varchar(36)) as myProductGUID, UpdatedDate, _Manufacturer, _Clearance, _Promotion, _HidePriceOnline, ProductType, Status, DATALENGTH(ProductPicture) as myImageSize, AvailOnWeb, SalesCategory, ProductClassID, ListPrice, Taxable, SalesUnit, Weight, Length, Color, Height, Width, Size, UPC, AltDescription, Description, OuterPackQty, TechSpec, ProductPicture , ROW_NUMBER() OVER (ORDER BY GUIDProduct) AS RowNumber FROM Product where UpdatedDate > '2013-11-14') SELECT * FROM MyProducts where RowNumber between 1 and 200

Open in new window

The query is kind of complicated, because it returns 200 rows at a time from a dataset of recently updated records. The subquery (MyProducts) has 20,000 rows for this client.

On another client (client B), the same query takes less than one second and MyProducts subquery has 40,000+ records. They are both on the same webserver.

How could I trace to see if the issue is with the internet connection or if there are problems with their SQL server?

Lynn Thames

ASKER

Update -

This information might help -

This query takes over 3 minutes -

WITH MyProducts AS (SELECT ProductID, cast(GUIDProduct as varchar(36)) as myProductGUID, UpdatedDate, _Manufacturer, _Clearance, _Promotion, _HidePriceOnline, ProductType, Status, DATALENGTH(ProductPicture) as myImageSize, AvailOnWeb, SalesCategory, ProductClassID, ListPrice, Taxable, SalesUnit, Weight, Length, Color, Height, Width, Size, UPC, AltDescription, Description, OuterPackQty, TechSpec, ProductPicture , ROW_NUMBER() OVER (ORDER BY GUIDProduct) AS RowNumber FROM Product where UpdatedDate > '2013-11-14') SELECT * FROM MyProducts where RowNumber between 1 and 200

Open in new window

But this query takes about a second -

SELECT top 200 ProductID, UpdatedDate, _Manufacturer, _Clearance, _Promotion, _HidePriceOnline, ProductType, Status, DATALENGTH(ProductPicture) as myImageSize, AvailOnWeb, SalesCategory, ProductClassID, ListPrice, Taxable, SalesUnit, Weight, Length, Color, Height, Width, Size, UPC, AltDescription, Description, OuterPackQty, TechSpec, ProductPicture FROM Product where UpdatedDate > '2013-11-14'

Open in new window

It is returning the same exact information on the first pass (rows 1 - 200), so can I assume this is not the internet connection?

Thanks!

ASKER CERTIFIED SOLUTION

strivoli

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

loki0609

Check your indexing. Whenever I've had slow queries it was because the row i was doing the where clause wasn't indexed.