Optimizing a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Optimizing a query

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2005-07-10 : 12:46:37

I would like to optimize this query/sproc if possible. It takes just over two minutes on a decent server.

The DDL:

Table Vouchersdetail: Compound PK on Voucher (INT) ,Linenumber (TINYINT), index on ServiceDate (DATETIME)

Table WordProcessor : Compound FK on VoucherNo (INT), Linenum (TINYINT), NOTE (TEXT), has .RTF documents, each row is approx. 20K-50K.

There is no full-text indexing. This sp is run once a month or less.

The INSERT INTO Birad .... SELECT is the crux of the query, and is what's taking most of the time. That statement could use optimizing.

What the sp basically does is search all documents corresponding to a certain time frame, and for a type of report (BIRAD) , then searches those documents to see which dr signed it (all entries in the #Docs table), and what level result it had (entries in the #Birs table), and logs it into a summary table.

The sp would be executed like this:


EXEC BiradList '1/1/2005','3/31/2005','Doe,Smith,Green,Black,','III,3,IV,4,V,5,'


CREATE PROCEDURE BiradList @From DATETIME,@To DateTime,@Dr VARCHAR(1000),@Birad VARCHAR(20)
AS
SET NOCOUNT ON
CREATE TABLE #Docs (LastName CHAR(25))
CREATE TABLE #Birs (BLevel CHAR(3))
DECLARE @ListPosition INT,@DrValue CHAR(25),@BValue VARCHAR(3)
 	WHILE PATINDEX('%,%',@Dr) <> 0 
  		BEGIN
  		SELECT @ListPosition =  PATINDEX('%,%',@Dr)
  		SELECT @DrValue = LEFT(@Dr, @ListPosition - 1)
  		INSERT INTO #Docs (LastName) SELECT @DrValue
  		SELECT @Dr = STUFF(@Dr, 1, @ListPosition, '')
  		END
SET @ListPosition = 0
	WHILE PATINDEX('%,%',@Birad) <> 0
		BEGIN
  		SELECT @ListPosition =  PATINDEX('%,%',@Birad)
  		SELECT @BValue = LEFT(@Birad, @ListPosition - 1)
  		INSERT INTO #Birs (BLevel) VALUES(@BValue)
  		SELECT @Birad = STUFF(@Birad, 1, @ListPosition, '')
  		END

INSERT Birad (PatID,ServiceDate,CPT,BLevel,Dr) SELECT W.PatID,VD.ServiceDate,VD.ProcCode,
(CASE B.BLevel WHEN 'I' THEN 1 WHEN 'II' THEN 2 WHEN 'III' THEN 3 WHEN 'IV' THEN 4 WHEN 'V' THEN 5 ELSE B.BLevel END),D.LastName
FROM WordProcessor W INNER JOIN Vouchersdetail VD ON VD.Voucher = W.VoucherNo AND VD.LineNumber =
W.LineNum INNER JOIN #Birs B ON PATINDEX('%BIRAD%CATEGORY ' + LTRIM(B.BLevel)+ '%',W.Note) > 0 
INNER JOIN #Docs D ON PATINDEX('%' + RTRIM(D.LastName) + '%',W.Note) > 0 WHERE VD.ServiceDate BETWEEN @From AND @To
AND W.Note LIKE '%BIRAD%' AND NOT EXISTS(SELECT * FROM Birad WHERE PatID = W.PatID AND ServiceDate = VD.ServiceDate)

IF @@Error <> 0 GOTO Ex_Proc

SELECT B.PatID,B.ServiceDate,B.CPT,B.BLevel,B.Dr,B.Letter,B.Result,
RTRIM(P.LastName) + ', ' + P.FirstName AS PtName,P.Account FROM BIRAD B INNER JOIN Patients P ON B.PatID = P.ID
WHERE B.ServiceDate BETWEEN @From AND @To AND BLevel IN (SELECT BLevel FROM #Birs) ORDER BY P.LastName,P.FirstName

Ex_Proc:
DROP TABLE #Birs
DROP TABLE #Docs
SET NOCOUNT OFF
RETURN

Sarah Berger MCSD

Kristen
Test

22859 Posts

Posted - 2005-07-11 : 09:08:32

Haven't looked at it very closely, but I always have a PK on temporary tables. Also, they might run faster as @TableVars, rather than #TempTables

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-11 : 09:21:02

how about some formatting / indenting to make that big select in the middle readable?

BUt I guarantee you the biggest issue you have is all of those joins on PATINDEX expressions, as well as the LIKE criteria that starts with a wildcard ... None of those can make use of any indexes at all.

It might be better to give us actual DDL statements and some INSERTS with a good amount of sample data so we can understand how your data is structured.

- Jeff

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2005-07-24 : 22:45:13

PK's on the temp tables would be a waste. Both tables hold 5-10 rows at the most.

Table variables are not available in SQL Server 7. Besides, they wouldn't make much of a difference in this scenario.

Here's the "big select" made more readable:


INSERT Birad (PatID,ServiceDate,CPT,BLevel,Dr) 
SELECT W.PatID,VD.ServiceDate,VD.ProcCode,D.LastName,
(CASE B.BLevel WHEN 'I' THEN 1 WHEN 'II' THEN 2 WHEN 'III' THEN 3 WHEN 'IV' THEN 4 WHEN 'V' THEN 5 ELSE B.BLevel END)
FROM WordProcessor W INNER JOIN Vouchersdetail VD 
ON VD.Voucher = W.VoucherNo AND VD.LineNumber = W.LineNum 
INNER JOIN #Birs B ON PATINDEX('%BIRAD%CATEGORY ' + LTRIM(B.BLevel)+ '%',W.Note) > 0 
INNER JOIN #Docs D ON PATINDEX('%' + RTRIM(D.LastName) + '%',W.Note) > 0 
WHERE VD.ServiceDate BETWEEN @From AND @To
AND W.Note LIKE '%BIRAD%' 
AND NOT EXISTS(SELECT * FROM Birad WHERE PatID = W.PatID AND ServiceDate = VD.ServiceDate)

The DDL: (will skip columns not used in query)


CREATE TABLE Vouchersdetail 
(PatID INT NOT NULL,Voucher INT NOT NULL,LineNumber TINYINT NOT NULL, ServiceDate DATETIME NOT NULL,ProcCode CHAR(10))

ALTER TABLE Vouchersdetail ADD CONSTRAINT PK_VouchLine PRIMARY KEY (Voucher,Linenumber)

CREATE CLUSTERED INDEX IX_PatID ON Vouchersdetail(PatID)

CREATE INDEX IX_ServiceDate ON Vouchersdetail(ServiceDate)

CREATE TABLE WordProcessor 
(PatID INT NOT NULL,VoucherNo INT NOT NULL,LineNum INT NOT NULL,Note TEXT)

CREATE UNIQUE CLUSTERED INDEX IX_VoucherLine ON WordProcessor(voucherno,Linenum)

CREATE INDEX IX_PatIDWP ON WordProcessor(PatID)

The Voucherno/LineNum columns in Wordprocessor are not in a FK constraint, but they are technically foreign keys for the Voucher/Linenumber columns in Vouchersdetail. The PatID indexes are used sometimes when retrieving lists of data for specific patients, and are FKs to a Patients table.

I cannot supply sample INSERTs or sample data because the Wordprocessor table gets populated with .rtf documents. This can only be reproduced by writing your own BLOB code or running the TEXTCOPY utility multiple times to populate the table.

I understand the biggie problem here is the use of LIKE and PATINDEX. Please note that indexes wouldn't make a difference as this is a TEXT column. The only usable index would be full-text indexes, which alas! I can't do at the moment.

The thing to do here would be to restrict the rows using indexes and optimizer hints so that the least amount of rows end up getting scanned using LIKE/PATINDEX.

The odd thing is that the plan is showing that 92% of the cost is in the index seek on Vouchersdetail.Voucher = Wordprocessor.VoucherNo AND Vouchersdetail.Linenumber = Wordprocessor.LineNum. It's not showing the LIKE/PATINDEX as being the bottleneck.

Does that mean it's as optimized as possible?

[Exasperated] So why does it take so long to run?????

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources