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 'Constatering' 
       ELSE '' END) AS IsManco 
,(CASE WHEN t1.IsOpgelost ='true' THEN 'Direct opgelost' 
       WHEN t6.verwijderd = 'true' THEN 'Verwijderd' 
       ELSE t3.name END) AS VerwerkingsGroep 
,(CASE WHEN (year(t4.DatumTechnischGereed)&lt;2000 or t4.technischgereed='false') THEN NULL     ELSE CONVERT(VARCHAR,t4.DatumTechnischGereed,20) END) AS DatumTechnischGereed 
,(CASE WHEN (year(t4.DatumAdministratiefGereed)&lt;2000 OR t4.administratiefgereed='false')     THEN NULL ELSE CONVERT(VARCHAR,t4.DatumAdministratiefGereed,20) END) AS     DatumAdministratiefGereed 
,(CASE WHEN (year(t4.DatumFinancieelVerwerkt)&lt;2000 OR t4.financieelverwerkt='false')     THEN NULL ELSE CONVERT(VARCHAR,t4.DatumFinancieelVerwerkt,20) END) AS     DatumFinancieelVerwerkt 
, (CASE WHEN t4.financieelverwerkt = 'true' THEN 'Ja' ELSE '' END) AS financieelVerwerkt 
, (CASE WHEN t4.AdministratiefGereed = 'true' THEN 'Ja' ELSE '' END) AS     AdministratiefGereed 
, (CASE WHEN t4.TechnischGereed = 'true' THEN 'Ja' ELSE '' END) AS TechnischGereed 
, t10.Name AS Aannemer 
, t6.verwijderd 
, t14.fullname AS MutatieDoor 
,(CASE WHEN (year(t4.MutatieOp)&lt;2000) THEN NULL ELSE t4.MutatieOp END) AS MutatieOp 
FROM MainTickets AS t1 
LEFT JOIN PrioCode AS t2 ON t1.PrioCode =t2.iD 
LEFT JOIN TicketVerwerking AS t4 ON t1.TicketNummer =t4.TicketNummer 
LEFT JOIN Verwerking AS t3 ON t4.VerwerkingsGroep =t3.ID 
LEFT JOIN Gebruikers AS t5 ON t1.AangenomenDoor =t5.ID 
LEFT JOIN TicketNummers AS t6 ON t1.TicketNummer=t6.TicketNummer 
LEFT JOIN Klanten AS t7 ON t1.Klant=t7.ID 
LEFT JOIN SubTickets AS t8 ON t8.TicketNummer =t1.TicketNummer 
LEFT JOIN Categorie AS t9 ON t9.ID =t1.Categorie 
LEFT JOIN Aannemers AS t10 ON t10.ID =t4.aannemeropdracht 
LEFT JOIN FormIDs AS t11 ON t8.formidcode=t11.FormIDcode 
LEFT JOIN OfferteAannemers AS t12 ON (t4.offertenummer=t12.offertenummer AND     t12.aannemer=t4.aannemeropdracht)
LEFT JOIN Operators AS t13 ON t4.operator=t13.id 
LEFT JOIN Gebruikers AS t14 ON t4.mutatiedoor =t14.ID 
ORDER BY t1.TicketNummer
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.