n********6
发帖数: 1511 | 1 Question1: What are the two ways to create the temptable? Which is the best
practice?
My answer:
Option1:
CREATE TABLE ##TEMPTABLE
...
INSERT INTO ##TEMPTABLE
SELECT ...
FROM ...
Option2:
SELECT ...
INTO ##TEMPTABLE
FROM ...
WHERE ...
I do not know which is the best practice. I think both can check the ##
temptable in tempdb first for validation, and both can create index after
data insert. |
|
n********6
发帖数: 1511 | 2 Would you explain a little bit how to use temp table and not use cursor in
this case?
Can you provide any comments if I do in this way? (MSSQL)
IF OBJECT_ID('TEMPDB..TEMPTABLE') > 0
DROP ##TEMPTABLE
CREATE ##TEMPTABLE
CREATE INDEX ... (IF NECESSARY, NONCLUSTERED PREFERRED IN GENERAL)
DECLARE CURSOR
OPEN CURSOR
FETCH CURSOR INTO @COLUMN_1, @COLUMN_2, @COLUMN_3
WHILE (@@FETCH_STATUS = 0)
SELECT FUNC(COLUMN_1, COLUMN_2), COLUMN_3
INTO (COLUMN_A, COLUMN_B)
FROM ORIGINAL_TABLE
FETCH NEXT FROM CURSOR |
|
n********6
发帖数: 1511 | 3 Ok. Since top is not available in Oracle, let me do in this way.
1. SELECT *
INTO TempTable
FROM PAYROLL
WHERE Salary = (SELECT MAX(SALARY) FROM PAYROLL)
And ID NOT IN (SELECT ID FROM TempTable)
2. SELECT Count = COUNT(*) FROM TempTable
3. Loop 2.3., when Count > 10, exit |
|
n********6
发帖数: 1511 | 4 Ok. Since top is not available in Oracle, let me do in this way.
1. CREATE TABLE TempTable
AS SELECT
FROM PAYROLL
WHERE Salary = (SELECT MAX(SALARY) FROM PAYROLL)
And ID NOT IN (SELECT ID FROM TempTable)
2. SELECT Count = COUNT(*) FROM TempTable
3. Loop 2.3., when Count > 10, exit |
|
B*****g
发帖数: 34098 | 5 (update似乎不一样也无所谓）
不知道这个能不能用，oracle下面sql必须加hint，否则t1,t2可能不一样。当然oracle
就不用这么做了，有现成的function。
WITH temptable (id, value, seqno)
AS
(
SELECT id, value, ROW_NUMBER() OVER (ORDER BY ID) AS seqno FROM table
)
SELECT t1.id, t1.seqno, SUM(t2.value)
FROM temptable, t1, temptable t2
WHERE t1.id = t2.id
AND t1.seqno >= t2.seqno
GROUP BY t1.id, t1.seqno |
|
i****a
发帖数: 36252 | 6 SQL 2005
I need to write 2 queries:
Query 1, blah blah blah
Query 2, I need to hit the rows that are NOT in Query 1
Method A:
Query 1, select blah into #temptable where blah = 1
Query 2, select blah not in (select blah from #temptable)
Method B:
Query 1, select blah where blah = 1
Query 2, select bah not in (select blah where blah = 1)
which one is more efficient? Is query optimize gonna make method B more
efficient? Is the only way to know to see query plan? |
|
t***u
发帖数: 368 | 7 blow query is VERY slow： > 15 mins, any suggestions appreciated !!
@tempTable ~ 20k data
#ExpTable ~ 500k data
declare @tempTable table
(
data_id int,
Q float,
Qtype int
)
create table #ExpTable
(
data_id int,
B1 float,
B2 float,
B3 float,
B4 float,
B5 float,
B6 float,
B7 float,
B8 float,
B9 float,
B10 float
)
... 阅读全帖 |
|
i****a
发帖数: 36252 | 8 写 Stored Procedure 批量处理数据
1.数数有多少记录需要被处理
select @n = count(*) from tableA where condition = xyz
2.用个functionGetNextRecord, 每次返回一个 ID
select top 1 ID from tableA where condition = xyz order by ID
3.写 WHILE loop
SET @count = 1
SET @NextRecord = functionGetNextRecord()
WHILE (@NextRecord IS NOT NULL) and (@count<=@n) BEGIN
blah blah blah
END
如果我写:
select ID
INTO #temptable
from tableA where condition = xyz
然后用#temptable做工就可以, 一次读表可以做到的硬是作成成千千万万次的读
另外不知道是不是老印的创作. 查 account balance, 没有一个 current bala... 阅读全帖 |
|
i****a
发帖数: 36252 | 9 写 Stored Procedure 批量处理数据
1.数数有多少记录需要被处理
select @n = count(*) from tableA where condition = xyz
2.用个functionGetNextRecord, 每次返回一个 ID
select top 1 ID from tableA where condition = xyz order by ID
3.写 WHILE loop
SET @count = 1
SET @NextRecord = functionGetNextRecord()
WHILE (@NextRecord IS NOT NULL) and (@count<=@n) BEGIN
blah blah blah
END
如果我写:
select ID
INTO #temptable
from tableA where condition = xyz
然后用#temptable做工就可以, 一次读表可以做到的硬是作成成千千万万次的读
另外不知道是不是老印的创作. 查 account balance, 没有一个 current bala... 阅读全帖 |
|
D******y
发帖数: 3780 | 10 【 以下文字转载自 Programming 讨论区 】
发信人: DrDonkey (DrDonkey), 信区: Programming
标 题: 问个best practice
发信站: BBS 未名空间站 (Wed Jan 7 20:53:45 2009), 转信
有一个table Products.
要求用户在web上搜索后返回随机顺序， 而且显示的时候要求带paging
请问我该怎么作？
是该一次把数据都从DB读出来，Populate到Product的object里面， 然后放到一个Arra
yList里面，Shuffle, 再从ArrayList里面提取objects来implement paging? 或者只把
ProductID放到Session里面，不过这要是Products table太大，也不太现实阿
还是该一次只读一部分数据（Items Per Page), 这样是不是必须用TempTable了阿(
SELECT
, then shuffle, then insert into temptable), 那岂不是每个用户session都要一个
tem
ptab |
|
D******y
发帖数: 3780 | 11 有一个table Products.
要求用户在web上搜索后返回随机顺序， 而且显示的时候要求带paging
请问我该怎么作？
是该一次把数据都从DB读出来，Populate到Product的object里面， 然后放到一个Arra
yList里面，Shuffle, 再从ArrayList里面提取objects来implement paging? 或者只把
ProductID放到Session里面，不过这要是Products table太大，也不太现实阿
还是该一次只读一部分数据（Items Per Page), 这样是不是必须用TempTable了阿(
SELECT
, then shuffle, then insert into temptable), 那岂不是每个用户session都要一个
tem
ptable了？
请诸位指点，谢了 |
|
W*******r
发帖数: 15 | 12 tempdb..temptalble resides in tempdb. #temptable resides in the same db. |
|
n********6
发帖数: 1511 | 13 Both can be used for temp table as a staging table. In user created stored
procedures, when and why do you use #table and ##table? What do you consider
in making a choice?
In my understanding,
##table exists until the server restart.
#table exists within the life cycle of the stored procedure.
Since in the end of the stored procedure the staging table (# and/or ##)
will be dropped, what makes the difference in choosing # or ##？ |
|
p*******d
发帖数: 359 | 14 好像#是local,##是global.
我也没用过##.不知道啥时候用.有人讲讲?
consider |
|
c*****d
发帖数: 6045 | 15 copy from sql doc
The two types of temporary tables, local and global, differ from each other
in their names, their visibility, and their availability.
Local temporary tables have a single number sign (#) as the first character
of their names; they are visible only to the current connection for the user
; and they are deleted when the user disconnects from instances of Microsoft
® SQL Server™ 2000.
Global temporary tables have two number signs (##) as the first characters
of their nam |
|
c*****d
发帖数: 6045 | 16 对于local temp table很好理解
对于global temp table,可以被所有的用户访问没问题
但是这句话"they are deleted when all users referencing the table disconnect
from SQL Server",我觉得有问题
我做的试验(1,2...6,7是步骤)
1. A session
create table ##demo
2. B session (could be same or different user from A)
select * from ##test -- table exists
3. C session (could be same or different user from A)
insert into ##test -- table exists
4. B session
select * from ##test -- table exists and could see new row
5. close A session
6. B session
select * fr |
|
n********6
发帖数: 1511 | 17 我都用过，用于stored procedure，然后作为scheduled job。都是按照以下步骤：
IF OBJECT_ID ('tempdb..##table')>0 -- 如果#就不需要。
DROP TABLE ##table -- 如果#就不需要。
step1: load data to # or ##,
step2: clean/process,
step3: load to final table/destination.
step4: drop ##table -- 如果#就不需要。
但是我不知道两种用法有何利弊。在实际应用中哪些情况下推荐用#，哪些情况下推荐
用##。 |
|
p*******d
发帖数: 359 | 18 我是能不用global就不用，有人讲讲打个比方啥时候该用这个。
disconnect |
|
|
n********6
发帖数: 1511 | 20 Note: Following logic do not have dynamic sql.
Advantage: Do not need to learn dynamic sql. Save time. You already know how to use cursor. Just use cursor to solve the problem.
Disadvantage: Loose the opportunity to learn dynamic sql.
第一步：
query on T1, T2 得到下面要查询的一些表的名字
对每个要查询的表， 找出表中满足条件的记录 r1, r2... rn,
把这些记录中放到自己建的一个表 T
SELECT ...
INTO ##TABLENAME -- Put all the tablesnames into a temptable.
FROM T1 JOIN T2 ON ...
WHERE ... |
|
n********6
发帖数: 1511 | 21 还可以先load to temptable, 处理好以后，再insert.这样对production table影响小。 |
|
u*********e
发帖数: 9616 | 22 thanks for responding. I use VS to design the rdlc file. It's was a straight
Fields!Stmt_Created_Date.Value drop in textbox. The field of "Group_Code"
and "Group_Member_Code" can be displayed without any problem but not for the
rest.
I did some research. One person raised the similar issue like mine. His
storeprocedure created a table variable and used cursor to combine different
rows into one row then put into the table variable and select the result as
the return set. That's very much like min... 阅读全帖 |
|
g***l
发帖数: 18555 | 23 你这个SP写得够烂的，怎么这么多的变量，还那么多CURSOR，难怪会出问题。多搞几个
TEMPTABLE也别用CURSOR，否则容易出错，时间比较用DATETIME，不要倒成VARCHAR去比
，容易出错。就是出个REPORT么，还CATCH什么。 |
|
c*******o
发帖数: 1722 | 24 在一个stored procedure里，需要用一个sub query
找到最近的exchange rate
select datadate, price/a.rate
from #tempTable, exchangeTable as a
where a.date_ =(
select max(date_)
from exchangeTable
where date_ <= datadate
~~~~~~~~~~
)
and datadate between @start and @end
上面query的问题是datadate在sub query里面不能
reference. 有什么好的解决办法么？谢谢。 |
|
s**********o
发帖数: 14359 | 25 select datadate, price from #tempTable
select date_, rate from exchangeTable
你有一个@start @end，因为时间的问题，MATCH不上，所以只好找最邻近的之前的RATE
，这个是问题的关键所在 |
|
m*****k
发帖数: 731 | 26 你一次populate #temptable 后咋个用它？
也要循环多次读还是上cursor?
你测了你的solution能快多少么？
如果有显著提高，那就值得refactor,否者费力不讨好还得罪人哦。 |
|
m*****k
发帖数: 731 | 27 你一次populate #temptable 后咋个用它？
也要循环多次读还是上cursor?
你测了你的solution能快多少么？
如果有显著提高，那就值得refactor,否者费力不讨好还得罪人哦。 |
|
D******y
发帖数: 3780 | 28 thanks les
you are talking about using temp table?
i thought of select all records, then shuffle, then insert all record into a
temptable, so the records are all random sorted. |
|
