Retrieve data from temporary table with dynamic name?
-
Quite right Vladan - my brain has shut down for Christmas. I don't think I should be coding accounts posting today - probably bankrupt somebody! Think I'll just go home.
-
It's nice to hear this kind of arguments.
There are a few rules that I use for temporary variables tables.
If sql has exception statements and not writing big tables to table variables it is fine.
I left most of my tempdb for sorting and grouping while I use my server memory efficiently. I have to agree with you if developer is not careful enough using these variables it can bring down server but it is a judgment call for Architect, Team Lead and QA.
I recommend in this case because looks like it is not a big result set.
-
Well, I don't need it at all 🙂
but as to the original question, no, I don't think this would help. There are MANY such tables created, all with the same design... lets say dummy1, dummy2,dummy3, dummy4 etc etc. And now, the question is how to write one SQL statement so, that it would work on ANY of these tables, and how to pass the table name as a parameter. This is simply impossible.
BTW, I use table variables myself, they are very handy sometimes, they just don't help in this particular problem.
-
Remember that all tables, permanent, temporary or table variables are held in memory as much as space permits. They (including table variables) are written to disc when memory space runs out. Table variables and conventional temporary tables work more or less identically so there should not be a performance difference. My understanding is that the main difference is that table variables do not get an entry in tempdb sys tables.
-
Before you start using table variables... study Q3/A3 and Q4/A4 of the following link...
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
...and, yes, there can be a huge difference in performance... because table variables do not use and cannot be made to use statistics, table variables can drag performance right into the lake.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
My table already have above 2.000.000 records so, when i create reports use this table it go up 10 minutes to produce report
and one more things, there are a lot of people do the same process at same time too , i must find a way to speed up my store procedure ... i end up with temporary with decrease the time to execute this store to about 3 minutes but it still too slow... someone have any ides better , please tell me.Any hint or tip to use index will help too.
Thanks and regards
SoL
-
Sol,
Recommend you post the CREATE TABLE statement for the table with 2,000,000 rows in it and the proc that creates the report. Be sure to include the code for any indexes that may be available and the Primary Key for the table. A handful of sample rows from the table and an example of what the report will look like would be very helpful, as well.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry , I can't post table and query because it to long and complex according to business rule which i must explain it if i want you understand what i mean. So, if you have meet the same problem, please tell me your experience and any hints, tips , articles which value with you then show me too. It will help me very much.
Thanks
SoL
-
Kinda like when your car runs bad but you won't let the mechanic pop the hood, isn't it Sol? Heck, I don't even know what your "car" looks like...
Ok, here's a couple of hints... if you have any cursors, While loops, or UDF's (there are some exceptions) in the code, or "column = @variable" or "@variable AS columnName" as part of a SELECT list, you're probably doing something wrong. If you are calling any store procedures that accept anything other than a date range as parameters, you're probably doing something wrong. If you are using views of views, you are probably doing something wrong. If you are using views that return more columns than what you need, you are probably doing something wrong. If you have any triangular joins or correlated subqueries, particullarly if the correlated subqueries are in the Where clause, you are probably doing something wrong (there are some minor exceptions). If you are using WHERE IN, you are probably doing something wrong. If you are using derived tables that are more that 2 deep, you are probably doing something wrong. If you are using table variables (even in a UDF), you are probably doing something wrong. If you are using a separate process to calculate grouped sub-totals and grand totals, you are probably doing something wrong. If you have any joins that join more than a small handful of tables, you are probably doing something wrong. If your report script has any RBAR in it, you are probably doing something wrong. If your table does not have a primary key on it, you are probably doing something wrong. Other than the primary key, if your table has more than 1 single column index, you are probably doing something wrong. If your code has any calculations on the left side of the "=" sign in a Where clause, you are probably doing something wrong. If you have any counters that count "rows", you are probably doing something wrong. If your report is to provide data to a spreadsheet, you are probably doing something wrong. If your report produces more than 2 lines per customer, you are probably doing something wrong. If you find it necessary to use a Global Temporary Table, you are probably doing something wrong. If you find it necessary to use dynamic table names, you are probably doing something wrong.
Lookup "cross-tabs", "reporting", "temp tables", "indexes", "blocking", "table hints", "dates", "aggragates", and "derived tables" on this forum and in Books Online.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Well, Thanks very much. You give me a lot of question to answer.
I will follow your suggest and see where i wrong.
Thanks again
.SoL
-
You bet, Sol,
Sorry for the apparent rambling
in my last post... was a bit late and I just started typing without much thought to the format.If you still need help with anything, please post back and I'll try not to ramble as much
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply