Improving a Query with a Stored Procedure (PHP examples included)

Heute mal etwas ganz anderes, weil es mir gerade über den Weg läuft.

################################################

This one should be quite obvious, but I actually stumbled upon this example in one of our projects and guess there will be more people out there who could make use of a little performance tip.

One of our customers was complaining about timeouts in one of his webservices due to long running queries in SQL (We’re talking Microsoft SQL Server 2005).

Looking into it I found a snippet looking like this:

$query_ba = „SELECT some, columns FROM BigTable bt INNER JOIN ReallyBigTable rbt ON bt.id = rbt.fk WHERE some = ‚$some‘ AND columns = ‚$cols‘ order by some, columns“;

The query ran quite long and it seemed clear there had to be a way to NOT read the whole Join and filter it afterwards with the PHP-Variables $some and $cols. To do this, create a Stored Procedure (SPROC) in Microsoft SQL Server Management Studio. In our example looking like this:

USE [Database]
GO
/****** Objekt:  StoredProcedure [dbo].[SPROC_name_of_sproc]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        Murer, Jens
— Create date: 2010 10 29
— =============================================
CREATE PROCEDURE [dbo].[SPROC_name_of_sproc]
— Add the parameters for the stored procedure here
@some varchar(255),
@cols varchar(255)
AS
BEGIN
SET NOCOUNT ON;

— Insert statements for procedure here
SELECT     some, columns
FROM        BigTable bt INNER JOIN ReallyBigTable rbt
ON bt.id = rbt.fk and bt.some=@some and rbt.columns=@cols
END

Leading SQL Server to drastically minimizing the join due to the constrains in the ON-clause.

In PHP the line mentioned above transforms to

$query_ba = „EXEC [dbo].[SPROC_name_of_sproc] @some = N’$some‘, @cols = N’$cols'“;

The resultset of this behaves like any other you would get out of a direct query.

Hope this helps.

###################################################################

Jens Murer works as a software developer at Cenesco GmbH in Lünen, Germany. Apart from this he is interested in social marketing. In his free time he takes pictures at renaissance fairs and concerts.

Advertisements

Über Jens Oliver "dae" Murer
I'm a software-developer and amateur photographer with a special interest in renaissance faires and event photography.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: