SQL Server Performance: Query Tuning vs. Process Tuning

ELEMENT_LOOP:

--INSERT ELEMENT
INSERT INTO dbo.tbl_Element (CatID, ElementNumber, ElementContent)
SELECT CatID, SUBSTRING (CatBody, 1, 4), SUBSTRING (CatBody, 8, CONVERT(INT, SUBSTRING (CatBody, 5, 3)))
FROM dbo.tbl_RecordCategory
WHERE CatBody IS NOT NULL

--REMOVE BODY IF ALL ELEMENTS ARE HANDLED
UPDATE dbo.tbl_RecordCategory
SET CatBody = NULL
WHERE LEN(CatBody) = CONVERT(INT, SUBSTRING(CatBody, 5, 3)) + 7

--REMOVE ELEMENT FROM BODY
UPDATE dbo.tbl_RecordCategory
SET CatBody = SUBSTRING (CatBody, CONVERT(INT, SUBSTRING(CatBody, 5, 3)) + 8, LEN(CatBody) - CONVERT(INT, SUBSTRING(CatBody, 5, 3)) + 7 )
WHERE CatInhoud IS NOT NULL

SET @Rows = @@ROWCOUNT
--IF NOT ALL ELEMENTS ARE HANDLED, LOOP AGAIN
IF @Rows > 0 GOTO ELEMENT _LOOP

This code splits the elements into readable pieces for the database. In order to populate the tables in the database, the following view gives the results in a table-like record set:

SELECT
C.BerichtID AS 'BerichtID',
C.Nummer AS 'Categorie',
C.Volgorde AS 'Volgorde',
MAX(CASE E.ElementNumber WHEN '0110' THEN E.ElementContent ELSE NULL END) AS 'E0110',
MAX(CASE E.ElementNumber WHEN '0120' THEN E.ElementContent ELSE NULL END) AS 'E0120',
MAX(CASE E.ElementNumber WHEN '0130' THEN E.ElementContent ELSE NULL END) AS 'E0130',
FROM dbo.tbl_RecordCategory C
LEFT JOIN dbo.tbl_Element E ON C.CatID = E.CatID
WHERE C.CatNumber = '01'
GROUP BY C.CatNumber, C.SortOrder, C.RecordID

The whole process loops through all categories and each element in them. This is about 20 loops each, hence 40 database executions. A file of 100,000 records is completely handled in the database in about two minutes. The alternative, handling each record separately takes close to an hour. By changing the process, the performance is 25 times faster.

Conclusion

As illustrated, there can be a great performance boost by altering the data-process. There is often more than one way to insert or manipulate the data in an OLAP database. By trying more than one method, insight can be gained on the options, flexibility, and processing speed. When working with large datasets, the objective is to handle as many records as possible in one set. This may result in a significant performance gain.

About the Author

Nils Bevaart has been a DBA for over seven years and has had hands-on experience in server management, database design, data warehousing, and performance tuning. Starting in 2006, he set up a company specializing in data processing, covering issues of effective maintenance, dataflow, database architecture, consultancy, and training.