Speed Up Data Transfer from Database to Application

sql server

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)<2000 or t4.technischgereed='false') THEN NULL     ELSE CONVERT(VARCHAR,t4.DatumTechnischGereed,20) END) AS DatumTechnischGereed 
,(CASE WHEN (year(t4.DatumAdministratiefGereed)<2000 OR t4.administratiefgereed='false')     THEN NULL ELSE CONVERT(VARCHAR,t4.DatumAdministratiefGereed,20) END) AS     DatumAdministratiefGereed 
,(CASE WHEN (year(t4.DatumFinancieelVerwerkt)<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)<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

Best Answer

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.

Related Solutions

Sql-server – Joining two inline functions, slows considerably when filtering on second function

Workaround suggestion:

If you can change the functions, try making them multi-statement table value function instead of inline functions. When doing so, you define an output table in the RETURNS part. In the DDL of the output table, declare a good clustered primary key.

Example:

CREATE FUNCTION dbo.fn_test1(@i int)
RETURNS @out TABLE (
    a    char(5) NOT NULL,
    b    date NOT NULL,
    c    int NOT NULL,
    PRIMARY KEY CLUSTERED (a, b)
)
AS

BEGIN;
    INSERT INTO @out (a, b, c)
    SELECT x, y, z
    FROM dbo.someTable
    WHERE someCriteria>@i;

    RETURN;
END;

Even though swapping an inline function for a multi-statement function comes with its own set of advantages and drawbacks, an important difference in your case may be that the output is stored in a temp table with a clustered index before the join happens. If you've properly aligned the clustered indexes on the two functions, joining their output tables should be a speedy affair.

Here's an idea of what kind of query plan you could expect (excluding the inner workings of the functions, obviously).

Example query plan

SQL Server – Changing Return String by Concatenation

Given tables:

CREATE TABLE dbo.Vendors
(
    VendorID integer NOT NULL,
    VendorName nvarchar(50) NOT NULL,

    CONSTRAINT [PK dbo.Vendors VendorID]
    PRIMARY KEY CLUSTERED (VendorID),

    CONSTRAINT [UQ dbo.Vendors VendorName]
    UNIQUE NONCLUSTERED (VendorName)
);

CREATE TABLE dbo.Invoices
(
    InvoiceID integer NOT NULL,
    InvoiceTotal money NOT NULL,
    VendorID integer NOT NULL,

    CONSTRAINT [PK dbo.Invoices InvoiceID]
        PRIMARY KEY CLUSTERED (InvoiceID),

    CONSTRAINT [FK dbo.Invoices VendorID]
        FOREIGN KEY (VendorID)
        REFERENCES dbo.Vendors (VendorID)
);

And sample data:

INSERT dbo.Vendors
    (VendorID, VendorName)
VALUES
    (1, N'Vendor #1'),
    (2, N'Vendor #2');
GO
INSERT dbo.Invoices
    (InvoiceID, InvoiceTotal, VendorID)
VALUES
    (1, $100.00, 1),
    (2, $200.50, 1),
    (3, $300.75, 1),
    (4, $123.45, 2);

Given the choice, you should generally prefer an inline table-valued function over a scalar or multi-statement function for performance reasons:

CREATE FUNCTION dbo.TotalBilledToVendor
(
    @VendorName nvarchar(50)
)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
    SELECT
        Total = SUM(I.InvoiceTotal)
    FROM dbo.Invoices AS I
    JOIN dbo.Vendors AS V
        ON V.VendorID = I.VendorID
    WHERE
        V.VendorName = @VendorName;

A helpful index is:

CREATE NONCLUSTERED INDEX
    [dbo.Invoices VendorID (InvoiceTotal)]
ON dbo.Invoices (VendorID)
INCLUDE (InvoiceTotal);

Query using a single VendorName:

SELECT
    TBTV.Total
FROM dbo.TotalBilledToVendor(N'Vendor #1') AS TBTV;

For multiple vendor names, use apply:

SELECT
    V.VendorName, 
    TBTV.Total
FROM dbo.Vendors AS V
CROSS APPLY dbo.TotalBilledToVendor(V.VendorName) AS TBTV;

If you must do formatting work in SQL Server, use either method in srutzky's answer, or STR:

SELECT
    N'£' + STR(TBTV.Total, 13, 2)
FROM dbo.TotalBilledToVendor(N'Vendor #1') AS TBTV;

Note the money and smallmoney types are not particularly popular. You may find decimal works better.

Best Answer

Related Solutions

Sql-server – Joining two inline functions, slows considerably when filtering on second function

SQL Server – Changing Return String by Concatenation

Related Question