|
As you can see below I am calling various user definded functions. When I removed two of my functions and replaced them with inline sql I gained 6 seconds for my query execution.
<br />
Select * from tableName<br />
dbo.fn_GetParameterSamples(tbl_project.ID," ","0") as ParameterList, <br />
dbo.fn_GetTemplateSamples(tbl_project.ID," ","0") as TemplateList, <br />
(select count(ID) from tbl_samples where tbl_Project.ID = id) as sampleCount,<br />
(SELECT COUNT(id) from tbl_samples where tbl_samples.ProjectID=tbl_Project.ID and isHold != 0 ) as SampleHold,<br />
What I would like to do is replace my other user defined funtions with inline sql(please look below at the sql). I seem to be having some trouble doing this. I would like to add the following line but I keep getting an error(Error 141: A Select statement that assigns a value to a variable must not be combined with data-retrieval operations) is there a way around this error? I believe removing the user defined funtions could improve the performance of my query.
<br />
(Select @ReturnParam = ISNULL(@ReturnParam + ', ','') + tbl_template.type from tbl_samples,tbl_template <br />
where tbl_Samples.projectID = tbl_project.ID and tbl_Samples.ID = tbl_template.sampleID) as TemplateLIst<br />
<br />
|
|
|
|
|
Hi
how i can find out that a column is FK?
how i can find out that a column is PM?
FK:Foreign Key
PM:Primary Key
|
|
|
|
|
HI,
I have a question about using a temp table in stored procedure. If for exmaple two individuals are using my applicaton and both press a button that calls the same stored procedure at roughly the same time will SQL Server know how to handle the creation of a temp table and perhaps wait unitl the first called sp is done executing. Or do I have to deal with some type of concurency check when using temp tables.
Thanks, I appreciate any advice that can be given to me.
|
|
|
|
|
Books Online has a full discussion of the under CREATE TABLE. It has a section on Temporary Tables that explains about the # table name prefix.
#MyTempTable: Local temp table visible only within the current SQL session. This is the one you want to use if you have multple users creating temporary tables with the same name.
##MyTempTable: Global temp table that all can see.
|
|
|
|
|
Thanks Michael, Thats exactly the information I was looking for. What is books online? I went to www.bookOnline.com... I must be going to the wrong place anyhow thanks for the info.
|
|
|
|
|
Books Online is the Transact-SQL help that is found in the SQL Query Analyzer that comes with SQL Server.
You will not have it if you are using MSDE only.
|
|
|
|
|
DOWNLOAD Books Online HERE[^]
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Hi all,
I'm hoping that somebody can help me with the performance of my a slow stored procedure that I have. This is a part of my scenario:
I have many Projects each project can have many samples and each sample can have many paramters. W
When I run my stored procedure to return all projects I have a funtion that I call on each row that gets all the samples(cursor) for a project. Then for each sample I get all the paramters(cursor).
Project -> samples -> paramters
for each sample I run through a cursor and concatnate a string of paramters that I find in a function I call.
With in the paramters function I run through the cursor and concatnate a string of paramters in the format of paramtername, paramtername, paramtername.
Now I have reason to belive this query is slow becuase of all the cursors we use. One of my collegues belives that the cursors are not the problem, infact he belives they are not slow at all that is all hype that cursors are poor in perforance is this right? Does anybody have any suggestions?
|
|
|
|
|
Cursors are slow. For example, last year I rewrote a stored procedure that used cursors to do the same thing without. I went from taking 20 minutes down to sub-one minute. A little bit more tweaking and the final result was about 7 seconds. So, while there may be other factors slowing down a stored procedure, eliminating cursors will almost certainly speed up things.
Remember that database systems are designed to work on sets. If you can give it a set of information to process then it works more efficiently than if you give it one row at a time.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Right now I am trying to figure out how I can eliminate the different cursors I have. I can not seem to think of the logic required to get say ten rows from a select statement and then concatnate a string based on a value from each row. Could this be done with a temp table? would a temp table be faster then a cursor? Is there perhaps a way to do this with a select statement to concatenate the string together kind of like when you use the sum method?
|
|
|
|
|
Here is some sample code to concat a strings without using a cursor. You will still have to loop through to append the results but it will eliminate one of your cursors.
DECLARE @ParamStr VARCHAR(1000)
SELECT @ParamStr = ISNULL(@ParamStr + ', ','') + ParameterName
FROM Parameters
PRINT @ParamStr
|
|
|
|
|
Thanks Michael this just might be what I'm looking for. I'll let you know how it turns out. My original query returns 318 records and takes about 1 min to run. I'm hopeing to knock it down to a few seconds.
|
|
|
|
|
Thanks for everyones help, I got my query down to 3 seconds from 1 min. I used a combination of a temp table and removed some user defined funtions that were absolutly useless and slowing my SP down. I normally hate working with SP's but I enjoyed learning about how to optomize a SP.
|
|
|
|
|
Hi, I am creating a dataset, by adding up to 10000 rows at a time. At present, I add each of these rows using a stored procedure. Is there anyway I can add the entire dataset instead of doing this row by row?
Thanks
|
|
|
|
|
Hi all,
How to make "ERD diagram" in SQL Server 2000.
plz discuss in detail
Thanx in advance
Sajjad
|
|
|
|
|
|
Hi,
Is there any way to define stored procedute to return nText(memo).
I need this to convert my memo field to my own data.
Any help?
Be simple and Be sample.
|
|
|
|
|
i would like to store farsi date in ms sql server, can any body know how to do it
ramesh
Gourigari Ramesh Reddy
Hyderabad
|
|
|
|
|
Hi there,
I cannot manage to use the greek character in mysql db.
I try using the greek character set and collation, but no way, it doesn't working.
Does someone have already success to work with mysql and non latin character ?
If yes can you help by giving me some advices plz.
Thx in advance.
|
|
|
|
|
hi
i imported an ms access db to SQL SERVER 2000 using the tool "import and export data".
Now all my relations from my access db are not shown or created in the sql server db.
How does this come? Is there a way to also import the relations? or do i have to lay them back one by one by hand?
grtz & thx
|
|
|
|
|
|
Hi all,
I wanna know about, r Sql Server 2000 support the Booleon data type, if yes plz tell how can it use...
and also tell about some common Data type like datatime,varchar,numeric...
Thanx in advance
Sajjad
|
|
|
|
|
imshally81 wrote:
Sql Server 2000 support the Booleon data type
Yes, in SQL Server it is called bit
imshally81 wrote:
tell about some common Data type
That's a vig vague. What do you want to know?
The base datatypes are:
binary Bigint bit Char datetime decimal Float image Int Money
nchar Ntext nvarchar Numeric Real smalldatetime smallint smallmoney sql_variant sysname text timestamp tinyint varbinary varchar uniqueidentifier
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thanx for reply,
I just wanna know small introduction of common data types and their usage....
Sajjad
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
