Query Performance problem - 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)

Query Performance problem

Author

Topic

maydo
Starting Member

20 Posts

Posted - 2005-07-18 : 13:37:22

Hi,

I have a "heavy" query which takes aprox. 3 sec. to execute.
The functions (fn_f1 and fn_f2) do some string operations (parsing, comparing)
The T1 table has aprox. 20000 records

The problem comes when the application does 2 requests to this query (SP) at the same time.
The execution time dobles.

Is anything wrong with this query ?

select
(
(dbo.fn_f1 (@param1, T1.mNAME) +
(dbo.fn_f2 (@param3, T1.mSTREET)

) AS PRCNT

from
T1

ORDER BY PRCNT desc

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-07-18 : 14:31:47

post your functions and your ddl. Is the order by neccesary? That willl usually cut some time out.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me through the forum.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-18 : 21:35:22

It could be that your query is extremely CPU intensive. If it is using 100% of available CPU power, that would cause execution time to double when the app make two calls to it at the same time.

I suspect that one or both functions is using a lot of CPU time, but since you did not post the code, it is hard to tell.

quote:
Originally posted by maydo

Hi,

I have a "heavy" query which takes aprox. 3 sec. to execute.
The functions (fn_f1 and fn_f2) do some string operations (parsing, comparing)
The T1 table has aprox. 20000 records

The problem comes when the application does 2 requests to this query (SP) at the same time.
The execution time dobles.

Is anything wrong with this query ?

select
(
(dbo.fn_f1 (@param1, T1.mNAME) +
(dbo.fn_f2 (@param3, T1.mSTREET)

) AS PRCNT

from
T1

ORDER BY PRCNT desc

CODO ERGO SUM

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-07-19 : 02:45:04

1) Do tou have a clustered index on the table?

2) Is the query still slow if you just get raw data and don't massage it via the functions?

3) Is the query still slow if you don't order it?

HTH

=================================================================
The most tyrannical of governments are those which make crimes of opinions, for everyone has an inalienable right to his thoughts. -Baruch Spinoza, philosopher (1632-1677)

maydo
Starting Member

20 Posts

Posted - 2005-07-19 : 05:09:44

Yes, it is CPU intensive.
Here the finction definition:

ALTER FUNCTION dbo.fn_MatchName (
@FirmaName nvarchar (255),
@DenialName nvarchar (255),
@MClustDiscnt int
)

RETURNS float
AS
BEGIN

-- local declarations
declare @FirmaNameW nvarchar (255)
declare @DenialNameW nvarchar (255)

declare @index1 int
declare @word1 nvarchar (255)
declare @wordCount1 int
declare @index2 int
declare @word2 nvarchar (255)
declare @wordCount2 int
declare @currentWordId2 int

declare @DenialUsedWords nvarchar(4000)
declare @found int
declare @delimiter varchar (5)
declare @matchValue int

-- intialisations
set @delimiter = ' '
set @wordCount1 = 0
set @wordCount2 = 0
set @DenialUsedWords = @delimiter
set @found = 0

-- empty
IF ISNULL(@FirmaName,'') = ''
RETURN 0

-- exact match
IF ltrim(rtrim(@FirmaName)) = ltrim(rtrim(@DenialName))
RETURN 100

-- init loop
set @FirmaNameW = ltrim(rtrim(@FirmaName)) + @delimiter
set @index1 = charindex(@delimiter,@FirmaNameW)

-- count denial words
set @DenialNameW = ltrim(rtrim(@DenialName)) + @delimiter
set @index2 = charindex(@delimiter,@DenialNameW)
set @wordCount2 = 0
while (@index2 > 0)
begin
set @wordCount2 = @wordCount2 + 1
set @DenialNameW = right(@DenialNameW,len(@DenialNameW)-@index2+1)
set @index2 = charindex(@delimiter,ltrim(@DenialNameW))
end

-- start loop
while (@index1 > 0)
begin
set @word1 = left(@FirmaNameW,@index1-1)
set @wordCount1 = @wordCount1 + 1

set @DenialNameW = ltrim(rtrim(@DenialName)) + @delimiter
set @index2 = charindex(@delimiter,@DenialNameW)
set @currentWordId2 = 0

while (@index2 > 0)
begin
set @word2 = left(@DenialNameW,@index2-1)
set @currentWordId2 = @currentWordId2 + 1
if (@word1 = @word2) and (charindex(@delimiter+cast(@currentWordId2 as varchar(3))+@delimiter, @DenialUsedWords) = 0)
begin
set @found = @found + 1
set @DenialUsedWords = @DenialUsedWords + cast(@currentWordId2 as varchar(3)) + @delimiter
break
end
set @DenialNameW = right(@DenialNameW,len(@DenialNameW)-@index2+1)
set @index2 = charindex(@delimiter,ltrim(@DenialNameW))
end

set @FirmaNameW = right(@FirmaNameW,len(@FirmaNameW)-@index1+1)
set @index1 = charindex(@delimiter,ltrim(@FirmaNameW))
end

--

if (@wordCount1 = @wordCount2) AND (@found = @wordCount1)
set @MClustDiscnt = 100

set @matchValue = @found * isnull(@MClustDiscnt,100) / @wordCount1

return @matchValue

quote:
Originally posted by Michael Valentine Jones

It could be that your query is extremely CPU intensive. If it is using 100% of available CPU power, that would cause execution time to double when the app make two calls to it at the same time.

I suspect that one or both functions is using a lot of CPU time, but since you did not post the code, it is hard to tell.

quote:
Originally posted by maydo

Hi,

I have a "heavy" query which takes aprox. 3 sec. to execute.
The functions (fn_f1 and fn_f2) do some string operations (parsing, comparing)
The T1 table has aprox. 20000 records

The problem comes when the application does 2 requests to this query (SP) at the same time.
The execution time dobles.

Is anything wrong with this query ?

select
(
(dbo.fn_f1 (@param1, T1.mNAME) +
(dbo.fn_f2 (@param3, T1.mSTREET)

) AS PRCNT

from
T1

ORDER BY PRCNT desc

CODO ERGO SUM

maydo
Starting Member

20 Posts

Posted - 2005-07-19 : 05:14:06

1. yes there is a clustered index on the table
2. the query is very fast when no function calls are used
3. the ordering does not decrease the performance in this case

quote:
Originally posted by Bustaz Kool

1) Do tou have a clustered index on the table?

2) Is the query still slow if you just get raw data and don't massage it via the functions?

3) Is the query still slow if you don't order it?

HTH

=================================================================
The most tyrannical of governments are those which make crimes of opinions, for everyone has an inalienable right to his thoughts. -Baruch Spinoza, philosopher (1632-1677)

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-19 : 16:12:09

Better select original columns and parse them on client. SQL is not really designed for that type of processing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources