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)
 Can anyone make this faster?

Author  Topic 

label
Posting Yak Master

197 Posts
Posted - 2003-01-31 : 10:50:51
I'm working with two tables here and the query posted below runs very slowly. (relatively speaking). Part of the problem is that Username is a varchar in one of the tables and an Int (UserId) in the other (Don't look at me, I didn't design these tables) so I can't define a relationship between them.

Anyway, if anyone has any suggestions on how to make the query below go faster I would be most thankful.

-------------------------------------------------------------------

CREATE PROCEDURE dbo.ap_get_user_by_modelDate

@modelname varchar(100),
@from_date datetime,
@to_date datetime

AS

Begin

--create temp table to put values in
Create table #temp(Userid varchar(50), Par_email nvarchar(64), Username nvarchar(64), cnt int, company nvarchar(64), industry varchar(64))

--Declare Variables need for Filling temp table
Declare @uid varchar(50),
@cnt int,
@fullname nvarchar(64),
@company nvarchar(64),
@industry nvarchar(64),
@paremail nvarchar(64)

Declare Cursid Cursor for
select distinct(username) from
smc_new_products.dbo.etechmodelrequests
where
modelname=@modelname
and
requestdatetime >= @from_date
and
requestdatetime <= @to_date
and
interfacename like '%download%'

Open Cursid
Fetch Next from cursid into @uid
while (@@fetch_status<>-1)
Begin

select @cnt=count(Username) from
smc_new_products.dbo.etechmodelrequests
where
Username=@uid
and
modelname=@modelname
and
requestdatetime >= @from_date
and
requestdatetime <= @to_date
and
interfacename like '%download%'

Select @fullname=(firstname + ' ' + lastname), @paremail=(right(email, (len(email)-charindex('@', email)))), @company=company_name, @industry=industry from
smc_new_products.dbo.usr_smc
where
convert(varchar(50), [User_id])=@uid

insert into #temp values (@uid, @paremail, @fullname, @cnt, @company, @industry)
set @paremail=''
set @fullname=''
set @company=''
set @industry=''

Fetch Next from cursid into @uid
End

Deallocate Cursid

select * from #temp order by cnt desc, par_email, Username

End
GO
-------------------------------------------------------------------

robvolk
Most Valuable Yak

15732 Posts
Posted - 2003-01-31 : 11:31:32
Well, you don't need the cursor, this should be a bit faster:

CREATE PROCEDURE dbo.ap_get_user_by_modelDate
@modelname varchar(100),
@from_date datetime,
@to_date datetime
AS
SET NOCOUNT ON
--create temp table to put values in
Create table #temp(Userid varchar(50),
Par_email nvarchar(64),
Username nvarchar(64),
cnt int,
company nvarchar(64),
industry varchar(64))

insert into #temp (UserID, UserName, Par_email, Company, Industry, cnt)
Select A.UserName,
(U.firstname + ' ' + U.lastname),
right(U.email, (len(U.email)-charindex('@', U.email))),
U.company_name,
U.industry,
A.cnt
FROM smc_new_products.dbo.usr_smc U INNER JOIN
(select username, Count(*) AS cnt from
smc_new_products.dbo.etechmodelrequests
where modelname=@modelname
and requestdatetime BETWEEN @from_date and @to_date
and interfacename like '%download%'
GROUP BY username) A
ON convert(varchar(50), [U.User_id])=A.UserName

select * from #temp order by cnt desc, par_email, Username
Go to Top of Page

mr_mist
Grunnio

1870 Posts
Posted - 2003-01-31 : 11:32:00
Let's go back a step here.

What is the purpose of the query?
Why are you using the cursor?
What's the DDL for the tables?


-------
Moo.
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-01-31 : 12:23:59
...And if Rob's sql correctly interpretes your business rules (you can tell because if you run yours then his the results should be identical),,,then you don't even need a temp table:

Select UserID, UserName, Par_email, Company, Industry, cnt
From (
Select A.UserName As UserID
, (U.firstname + ' ' + U.lastname) As UserName
, right(U.email, (len(U.email)-charindex('@', U.email))) As Par_email
, U.company_name As Company
, U.industry As Industry
, A.cnt
FROM smc_new_products.dbo.usr_smc U INNER JOIN
(select username, Count(*) AS cnt from
smc_new_products.dbo.etechmodelrequests
where modelname=@modelname
and requestdatetime BETWEEN @from_date and @to_date
and interfacename like '%download%'
GROUP BY username) A
ON convert(varchar(50), [U.User_id])=A.UserName) As XXX
by cnt desc, par_email, Username



Go to Top of Page

label
Posting Yak Master

197 Posts
Posted - 2003-01-31 : 12:25:45
quote:

Well, you don't need the cursor, this should be a bit faster:


Yeah, I figured there was probably a way to loose the cursor....and I also knew I was taking a substantial perf hit by doing it that way but I'm not nearly experienced enough in SQL to know how to do it differently.

That being said, I used your example and got the following error.

Server: Msg 207, Level 16, State 3, Line 20
Invalid column name 'U.User_id'.

The exact code I exec'd was this:

quote:

Begin
Declare @modelname varchar(100),
@from_date datetime,
@to_date datetime

set @modelname='KQ23001N'
set @from_date='01/01/03'
set @to_date='01/29/03'

SET NOCOUNT ON

--create temp table to put values in
Create table #temp(Userid varchar(50),
Par_email nvarchar(64),
Username nvarchar(64),
cnt int,
company nvarchar(64),
industry varchar(64))

insert into #temp (UserID, UserName, Par_email, Company, Industry, cnt)
Select A.UserName,
(U.firstname + ' ' + U.lastname),
right(U.email, (len(U.email)-charindex('@', U.email))),
U.company_name,
U.industry,
A.cnt
FROM smc_new_products.dbo.usr_smc U INNER JOIN
(select username, Count(*) AS cnt from
smc_new_products.dbo.etechmodelrequests
where modelname=@modelname
and requestdatetime BETWEEN @from_date and @to_date
and interfacename like '%download%'
GROUP BY username) A
ON convert(varchar(50), [U.User_id])=A.UserName

select * from #temp order by cnt desc, par_email, Username
End


It should be noted the the "username" field in the Modelrequest table is the same thing as the User_id in the usr_smc table though the username field is a varchar and the User_id column is an int in the usr_smc table.

Thanks for your advice.....I'd really like to get a better handle on some more advanced techniques and having real life examples makes it very easy for me to learn.

Note: One more thing, what does the "Set NOCOUNT on" do?




Edited by - label on 01/31/2003 12:29:49

Edited by - label on 01/31/2003 12:33:01
Go to Top of Page

label
Posting Yak Master

197 Posts
Posted - 2003-01-31 : 12:28:36
quote:

Let's go back a step here.

What is the purpose of the query?


Just to pull some basic user information which is determined by the following english query:

"Select all userid's from the modelrequest table where the userid downloaded a specific model and then grab the username and other info from the user_info table where the userid matches that of the userid's pull from the modelrequest table"


quote:
Why are you using the cursor?


Mostly because I'm not skilled enough to get around using it.

quote:
What's the DDL for the tables?


Not sure what a DDL is?


Edited by - label on 01/31/2003 12:28:56
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2003-01-31 : 12:37:24
Hmmmm. Try taking the brackets [] off of the column name (U.User_ID instead of [U.User_ID])

DDL stands for Data Definition Language, and is essentially the CREATE TABLE statement required to create the table you're defining or using in the query.

SET NOCOUNT ON suppresses the "x rows affected" messages that are broadcast after a command is executed. It is a good habit to get into when creating stored procedures to always SET NOCOUNT ON as the very first command. These messages can interfere with the proper functioning of database access layers like ADO, and they aren't necessary for the procedure to work.
Go to Top of Page

label
Posting Yak Master

197 Posts
Posted - 2003-01-31 : 12:53:43
quote:

Hmmmm. Try taking the brackets [] off of the column name (U.User_ID instead of [U.User_ID])


That worked! Thank you very much for your help. It increased the speed exponetially. Instead of taking 51 seconds to execute, it now takes 5 in the query analyzer and 1 second as a stored proc.

Thanks again.
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-02-03 : 08:30:48
I'm Just Curious did, you lose the temp table as well?
Go to Top of Page

label
Posting Yak Master

197 Posts
Posted - 2003-02-04 : 09:11:26
quote:

I'm Just Curious did, you lose the temp table as well?


Actually no, I didn't. I was just so pleased to get it running that much faster that I forgot to take out the Temp table before I moved it to production.

However, I did test out your solution and it worked perfectly so at some point I'll have to go back and clean up those stored procs.

Thanks for the advice!
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-02-04 : 09:19:10
GREAT!

And Congrats!

btw/ If you need temp tables, avoid them and use the table datatype instead (unless it's for massive amounts of data). Table datatypes reside in memory and #temp tables reside in tempdb.

Brett

8-)
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site