Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2013-11-15
3
207 Views
Last Modified: 2013-11-20
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?
0
Comment
Question by:lthames
3 Comments
 

Author Comment

by:lthames
ID: 39651314
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!
0
 
LVL 19

Accepted Solution

by:
strivoli earned 500 total points
ID: 39651482
Use the SQL Profiler to understand what happens, or, run the query locally and you will know how much it takes.
0
 
LVL 2

Expert Comment

by:loki0609
ID: 39652060
Check your indexing. Whenever I've had slow queries it was because the row i was doing the where clause wasn't indexed.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question