Database

Re: Temp tables in a stored procedure

12-May-05 6:24

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.

Michael Potter12-May-05 6:31

Michael Potter

12-May-05 6:31

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.

Re: Temp tables in a stored procedure

Rob Graham12-May-05 11:46

Rob Graham

12-May-05 11:46

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

Stored Procedures/Performance/Cursors

Fallen-Angel12-May-05 5:06

12-May-05 5:06

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?

Colin Angus Mackay12-May-05 5:30

12-May-05 5:30

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

Fallen-Angel12-May-05 5:41

12-May-05 5:41

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?

Michael Potter12-May-05 6:27

Michael Potter

12-May-05 6:27

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

Fallen-Angel12-May-05 6:38

12-May-05 6:38

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.

Inserting a dataset into SQL

Anonymous13-May-05 5:15

Anonymous

13-May-05 5:15

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.

totig12-May-05 2:22

totig

12-May-05 2:22

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

How to make ERD diagram

imshally8112-May-05 1:17

Re: How to make ERD diagram

12-May-05 1:17

Hi all,
How to make "ERD diagram" in SQL Server 2000.
plz discuss in detail
Thanx in advance
Sajjad

Colin Angus Mackay12-May-05 2:31

Stored Procedure - Return type nText

12-May-05 2:31

* Open Enterprise Manager
* Navigate to the required database
* Open the Diagrams leaf
* Right Click the Diagrams leaf and select "New Database Diagram..."
* The wizard will walk you through adding the tables that you want to start with.

Voila... A new diagram.

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

.NetRams12-May-05 1:04

.NetRams

12-May-05 1:04

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.

Date in Farsi

GouriGari RameshReddy12-May-05 0:48

GouriGari RameshReddy

12-May-05 0:48

i would like to store farsi date in ms sql server, can any body know how to do it
ramesh OMG | :OMG:

Gourigari Ramesh Reddy
Hyderabad

MySQL and non-latin character

Apusnaias11-May-05 4:27

Apusnaias

11-May-05 4:27

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.

Sql Server - MS Access

Timothy_198211-May-05 3:59

Timothy_1982

11-May-05 3:59

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

Re: Sql Server - MS Access

jasncab11-May-05 6:09

jasncab

11-May-05 6:09

You should have used DTS or the Upsizing Wizard.

Both are well documented and easy to use.

http://www.jasncab.com/huberblog :: Jason Huber

Data Type problem in SQL server 2000

imshally8111-May-05 2:27

11-May-05 2:27

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

Colin Angus Mackay11-May-05 2:46

11-May-05 2:46

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

imshally8111-May-05 20:43

11-May-05 20:43

Thanx for reply,
I just wanna know small introduction of common data types and their usage....
Sajjad

Colin Angus Mackay11-May-05 21:10

11-May-05 21:10

Have you tried to read the documentation that comes with SQL Server?

I'm willing to help if you have at least attempted to read the documentation and are having problems with it. But this sounds like you have not even attempted that.

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

imshally8111-May-05 21:18

DateTime between two times

11-May-05 21:18

ok....

Michael P Butler11-May-05 0:07

Michael P Butler

11-May-05 0:07

I have a table on an SQLServer 2000 box with a DateTime field called CallTime.

I want to select all the records between 19:00 and 06:59 over a period of days

Is there a more efficent way of doing the select in a stored procedure than this,

WHERE (DATEPART(hh, dbo.JobData.calltime) * 60 + DATEPART(mi, dbo.JobData.calltime) BETWEEN 
DATEPART(hh, '19:00') * 60 + DATEPART(mi, '19:00') AND DATEPART(hh, '23:59') * 60 + DATEPART(mi, 
'23:59')) OR (DATEPART(hh, dbo.JobData.calltime) * 60 + DATEPART(mi, dbo.JobData.calltime) BETWEEN 
DATEPART(hh, '00:00') * 60 + DATEPART(mi, '00:00') AND DATEPART(hh, '06:59') * 60 + DATEPART(mi, 
'06:59'))

Michael

CP Blog [^] Development Blog [^]

Re: DateTime between two times

Colin Angus Mackay11-May-05 2:48