| Author |
Topic |
|
simflex
Constraint Violating Yak Guru
327 Posts |
 Posted - 2009-05-21 : 18:17:39
|
| Dear gurus,Currently, we have the following process in place:1. Click to run a report2. Before report is generated, delete existing records from tTable3. Then insert into the zTable (field1,field2...fieldN) select value1, value2...valueN from anotherTable.We don't like this at all because it slows our server.Can you please recommend a better approach?Thanks a lot |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-05-21 : 18:33:52
|
you have to give us more details.What is tTable for and which query is slow ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2009-05-21 : 19:27:30
|
| Thanks a lot KH for your prompt response.Just right off the bat, I could tell I gave you wrong info. Sorry.There isn't a zTable.Just to clarify,Before we run a report, we delete all existing records from tTable.Then populate the tTable by selecting records from another table.Once this table is populated now, then run your reports from the tTable.So, basically, the tTable acts as our temp.We also feel that it is the reason query is slow.I was just wondering if there is another way to perhaps just query the main table directly and still get only the data that I need? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-05-21 : 21:09:34
|
quote:
I was just wondering if there is another way to perhaps just query the main table directly and still get only the data that I need?
Yes, why not query the main table directly ?On the usage of your tTable, why not create a actual temp table ? just prefix the table name with # when creating it.examplecreate table #temp( col1 int, col2 varchar(10)) temp table will be automatically drop when it is out of scope. Do you don't have to worry about cleaning up.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-22 : 03:37:06
|
| are you trying to capture each days updated data by this delete and insert process? if yes, it would be better to add a audit column in your main table like datemodified which will always take value of current system date for insert/update. then each day, before running report, just take all records from this table with datemodified > previous data to get changed records alone and compare with tTable. Then1. for records existing on both tTable and main table, update details onto tTable 2. For records existing on main but not on tTable , insert them onto tTable 3. for records existing on tTable but not on main, delete them from tTable |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2009-05-22 : 10:04:13
|
| Sure, both of you experts provide me with very useful information.Thanks very much.khtan,Since I have hardly used #temp table, will the create #temp table, load #temp table, etc happen all happen on the fly?Can you please give me a small example of where I could create temp table, load it with records I need from main table, pull my reports from this temp table and as soon as the user closes the report screen, this temp table drops is ready again for next action?You have some great ideas as well but what I am trying to do is get away completely from this tTable.Anyway of working around it?Many thanks to both of you. |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2009-05-22 : 10:10:08
|
| visakh16,Sorry there is no way to exit your text once you submit it that I am aware of.The comment that started with "You have some great ideas as well but... are attributed to you.sorry |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-05-22 : 10:11:36
|
see http://msdn.microsoft.com/en-us/library/ms174979(SQL.90).aspxthere is a section on Temporary Tables
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
Replacing Temp table