October 8, 2002 at 8:36 am
hi all!!
I need suggestions from you because am doing the insertion of a temporary table with EXEC (@string), this code is used by a stored procedure executed twice by second and this increase notoriously the use of tempdb, how can I improve my code ??
this is the example:
CREATE TABLE #DataResume(
variable1 varchar(20),
variable2 ultimo float)
SELECT @Cmd = 'EXEC master..xp_srvpt_lastdlymult @params)
INSERT#DataResume
EXEC (@Cmd)
Tnks for your help...
greetings
Engineer Programmer Jr.
Engineer Programmer Jr.
October 8, 2002 at 9:50 am
Use a permanent table instead.
Steve Jones
October 8, 2002 at 10:22 am
In the case that due to consulting and computing values its not possible to use a permanent table, what other option would I have??
Omar
Engineer Programmer Jr.
Engineer Programmer Jr.
October 8, 2002 at 10:46 am
You can explore using the table variable if you have SQL Server 2000. This would move the load from the tempdb to memory. I don't know if this will help or not, because it could use up all your memory. I think it is at least worth testing.
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 8, 2002 at 11:07 am
I would be careful using the table variable type - for one thing I don't believe it supports INSERT...EXEC, but more to the point, it is not optimisable within the query plan parser. So everytime you reference it, you'll see a table scan in the plan...
October 8, 2002 at 11:09 am
Actually, I've just noticed you're using the float datatype - I hope you know this is not accurate - in fact it can be very inaccurate depending on what you need to do. I also see you're calling an extended stored procedure - why is that?
October 8, 2002 at 4:08 pm
hi sjcsystems
I saw your suggestions and I have some questions for you, in your first point of view, the performance could be affected using table variable type if the result set of the execution of the XP are a few number of registers???
In your next points of view, why using float datatype could be inaccurate, in what sense??, I'm inserting directly the data that the XP displays
Engineer Programmer Jr.
Engineer Programmer Jr.
October 8, 2002 at 4:16 pm
I'm using a extended procedure because the information we need is retrieved from a DLL of one of our data sources, that is why we use the XP.
greetings
Engineer Programmer Jr.
Edited by - osoto on 10/08/2002 4:19:26 PM
Engineer Programmer Jr.
October 8, 2002 at 4:21 pm
OK float question, try this:
declare @x float,
@y numeric(16,10)
set @x=1.3333
set @y=1.3333
select @x
select @y
The answers speak for themselves...
table datatypes - you cannot force the query optimiser to do anything but a table scan on these. If it's a few values then it won't matter, as a scan will be quicker usually. Why can't you create a permanent table, then clear it out every time your code is run?
This isn't financial prices is it by any chance?, something like the Reuters SSL toolkit?
October 8, 2002 at 4:33 pm
thank you for your point of view sjcsystems, I understood your suggestions about float data type, usually we receive information with 4 decimals and I think this point doesn't matter.
With respect with table variable I think I must to prove this in order to see its possible advantages since the XP returns few values.
It's a good opinion about creating a permanent table, I will check it out this depending on the requirements and functionality of the process.
Engineer Programmer Jr.
Edited by - osoto on 10/08/2002 5:37:02 PM
Engineer Programmer Jr.
October 10, 2002 at 5:24 am
Hola osoto!
If you decide to use a permanent table, I'd suggest adding a column called SPID, and when you do your insert into the table, include @@SPID (process ID) as part of the INSERT. This way, several people can be simultaneously using your stored procedure without interfering with each other, as they will each have a separate Process ID.
Once you do the INSERT, then to use the results from the "permanent temporary" table, just do a SELECT ..... WHERE SPID = @@SPID
Also, before you exit the stored procedure, do a cleanup of the table with
DELETE FROM PermaTemp WHERE SPID = @@SPID
Best regards,
SteveR
Stephen Rosenbach
Arnold, MD
October 10, 2002 at 6:20 am
If you use the 'permanent' temporary table with the SPID column, you will want to clear it at the start of your procedure, not at the end. If you do not clear it at the start, and something happens to abort the process, the next user can get data that does not belong to them. Also, if you cluster on SPID, you will have fewer contention problems if the table is relatively full, rather then mostly empty.
October 12, 2002 at 3:51 am
Just a note that table variables are not in-memory structures the data is written to disk in the same manner as any other table. The benefits are due to the scoping and locking issues, i.e the get destroyed when the procedure finishes anddo not cause blocking of tempdb system tables when created.
Simon
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 14, 2002 at 8:07 pm
hi osoto,
By looking at your code, what not you try the following :
INSERT #DataResume
EXEC master..xp_srvpt_lastdlymult @params
instead of assign to @Cmd variable and execute it separately. I guess this will help to improve the performance?
I hope this make sense 🙂
cheers
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply