Why does In fix OR slowdown? (1 Viewer)

Rx_ · Dec 16, 2014

Had to try and post about 20 times...
It seems a s-e-lect Count statement in the Code posting would crash the internet for this site.

A view uses about 15 T-SQL functions. The entire query for 20,000 records was taking about 2 to 5 seconds.
Then two functions were added, it was taking over 1 minute!
At first, the Like statement with wild cards were suspect. They were replaced with about a 10% speed improvement.

Then, the OR statements were replaced with the IN.
This made the query drop from about 1 minute back to 2 to 5 seconds.

It is on a Text field. But really, that much of an overhead?

Any ideas would be very welcome.

Code:

-- the s-e-lect c-ount seems to crash any attempt to post
FROM Wells_SHLBHL 
WHERE (((Wells_SHLBHL.ID_Wells) = @ID_Wells) AND ((Wells_SHLBHL.SHLBHL) = 'BHL') 
AND (Wells_SHLBHL.Req_Fin)IN('Requested', 'Final' ) ) 
)

SQL_Hell · Dec 17, 2014

Hmm without knowing exactly what you changed it difficult to say, but..

1. A view that calls 15 functions? really!? functions as you already know perform badly.

2. why are you searching through a text field for those values? seems like a design issue

3. 2-5 seconds for a count of 20,000 is still pretty awful

Can you post what you changed exactly?

Rx_ · Dec 17, 2014

Good questions. Sorry I didn't post code yesterday. The internet would loose connection if code was added. Then, my VPN to the SQL Server kept dropping. Very fustrating day. I will post the solution next just in case the same internet problem comes back.

Rx_ · Dec 17, 2014

Constant computer reboots and network loss took my focus off the actual problem. The code below fixed it. The entire query runs in about 3 seconds again. Like a real nubie, the 2nd Parameter in the OR statement was left off. So, naturally, every record in the table was being Left and evaluated.
The other function shown in the attachment had the same problem. By using the IN( ) statement, it removed the OR.

Some of these functions have multiple parameters that are evaluated in CASE or nested logic. The View shown in the attachment returns a series of True/False as part of a rule engine. The T/F columns are then evaluated (not shown) to make determinations without human decisions.
The Rule engine runs in 2 modes. One is for all records to create Work-Flow Quality reports. The typical mode is for it to run for only 1 ID at a time every time the users make any data entries on any field on any form.
The 3 seconds is from the SQL Management Studio visual interface.
Once this becomes a Linked Table to Access, it is probably only 1 second for all records.

These two are so, so simple. Yet, I botched it up. This morning it was so obvious.

Will post this to see if my network is behaving first, then post the entire Scalar Function.

Code:

Set @CountResult = (SELECT   COUNT(*)
    FROM         Wells
    WHERE     (ID_Wells = @ID_Wells) AND (RIGHT(Well_Name, 3) LIKE '%H%') OR
       (ID_Wells = @ID_Wells) AND (RIGHT(Well_Name, 3) LIKE '%D%')
    )

Rx_ · Dec 17, 2014

Tried to post 15 lines of code with no comments. My internet connection totally crashed. I will come back later when things are working.
I blame it on too many employees watching viral Christmas videos.

Rx_ · Dec 17, 2014

Will see if I can post these 9 lines of code.
Had posted the Left and Like search on SQL Server Central.
Was reminded what a total Nubie I was for the solution above. Will share it.
It includes some Test data and a much simpler version of what I was trying to accomplish.

Code:

DECLARE @CountResult int, @ID_Wells int = 1
Set @CountResult = (SELECT   COUNT(*)
    FROM  (VALUES(1, 'SomethingH'),
                (1, 'Somethingd'),
                (1, 'SomethingD'),
                (1, 'SometHing'))Wells(ID_Wells, Well_Name) --TestData
    WHERE ID_Wells = @ID_Wells 
    AND   RIGHT(Well_Name, 3) COLLATE LATIN1_GENERAL_BIN  LIKE '%[HD]%'
    )
SELECT @CountResult

Rx_ · Dec 17, 2014

Network got a little better. This post kept timeing out.
This is the query above added into the Scalar function.
It is one of the more simple scalar functions. The suggested [HD] chooses either H OR D. The Latin chooses upper case as needed.
How did I get by without that for so long? If carrying a hammer, everything looked like a nail.

Code:

USE [YourDB]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[RE_R1073](@ID_Wells  int) 
RETURNS int
AS 
BEGIN; 
  DECLARE @CountResult as int; 
  DECLARE @intReturnValue as int;
   Set @CountResult = (SELECT   COUNT(*)    
    FROM         Wells
    WHERE     (ID_Wells = @ID_Wells) AND   RIGHT(Well_Name, 3) COLLATE LATIN1_GENERAL_BIN  LIKE '%[HD]%'
    )
if @CountResult >0
 set @intReturnValue = -1 --  -1 is True in Access if sending back to Access via linked table
else 
 set @intReturnValue = 0 --  0 is True in Access
 return @intReturnValue  -- value returned by function
END; 
GO

A side note: after looking this up, the conditions are reduced by half and binary collations are faster than others. I plan to use this much more often.

Why does In fix OR slowdown? (1 Viewer)

Rx_

Nothing In Moderation

SQL_Hell

SQL Server DBA

Rx_

Nothing In Moderation

Rx_

Nothing In Moderation

Attachments

Rx_

Nothing In Moderation

Rx_

Nothing In Moderation

Rx_

Nothing In Moderation

Similar threads

Users who are viewing this thread