Speed up data transfer from db to application

I have build an application that handles data stored in a MS SQL server database. This server is hosted externally of our company and has to be connected to via SSH because of company rules.

On a regular working day it takes about 7 minutes to retrieve a list of data, while it takes less than one minute to do the same outside office hours.

The database is relatively simple. There are 3 main tables which have a relation to each other on one field which is also set as primary key. These tables have relations with several other small tables where lists are stored. So the fields in the main tables are integer fields related to a small table where that integer points to a text field.

I'm trying to figure out why it takes 7 minutes during office hours and less than one 1 outside office hours.

There are about 12 users.

Anyone have some tips for me?

rg, Eric

Edit: SQL code:

```
SELECT
t1.TicketNummer
,t1.SiteNummer
,t9.name AS Categorie
,t7.name AS Klant
, (CASE WHEN t11.RapportNaam IS NULL THEN t5.FullName ELSE t5.FullName END) AS AangenomenDoor
, (CASE WHEN t11.RapportNaam IS NULL THEN t1.AangenomenOp ELSE t1.AangenomenOp END) AS AangenomenOp
, (CASE WHEN t11.RapportNaam IS NULL THEN t1.aangenomenop ELSE t8.UitgevoerdOp END) AS UitgevoerdOp
, (CASE WHEN t11.RapportNaam IS NULL THEN t5.FullName ELSE t8.UitgevoerdDoor END) AS UitgevoerdDoor
, (CASE WHEN t11.RapportNaam IS NULL THEN 'Via ESIT' ELSE t11.RapportNaam END) AS Rapport
,t8.VraagNummer
,t8.Uploaded
,t2.Name AS PrioCode
, t4.offertenummer
, t4.ponummer
, CASE WHEN t4.hersteldoor=0 THEN ''
WHEN t4.hersteldoor=1 THEN 'Aannemer'
WHEN t4.hersteldoor=2 THEN 'Eigen personeel'
WHEN t4.hersteldoor=3 THEN 'Operator'
ELSE 'Aannemer' END AS TeHerstellenDoor
, t12.refnraannemer
, t12.offertebedrag
, t13.name as Operator
, t4.operatorRefNr
,(CASE WHEN t1.IsManco ='true' AND t1.IsOpgelost = 'false' THEN 'Manco'
WHEN t1.IsConstatering ='true' THEN 'Co

During work hours, take a look at sys.dm_exec_requests and see what the wait_type column says. This will tell you what the requests are waiting for.

Right before working hours, you could run DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); and then look at sys.dm_os_wait_stats to see the accumulated stats during the day.

Do you have indexes defined on any of these tables? If not, then table scans might be clogging your I/O system, and locks might be causing some blocking.

If people are running SQL Profiler traces during work hours, those could be slowing down the entire system. You could detect those by quering sys.traces.

Running a careful profiler trace yourself might reveal some interesting facts about this query. Try capturing a Showplan XML Statistics Profile event when it runs. Maybe the query processor is choosing a very bad query plan. Maybe the plan is generating intermediate tables that have a ridiculous number of records, or maybe there is a bad nested loop that would work better with an index or with a merge join.

Since this is an external server, another possibility is that you are simply overwhelming network bandwidth during working hours.

You mentioned in a comment that there was a lot of data coming back. Adding some filters in the query might help-- if that is an acceptable solution for your application.

StackExchange Database Administrators Q#33081, answer score: 2