查询中有一列包含动态SQL，如何将这一列执行为具体的值？

me_child 2015-09-25 10:39:11

有一个表A：

ID TYPE SQL_TEXT
1 1202 SELECT PROID FROM PROJECT WHERE 1=1 AND CATEGORY=3301 AND USER_ID=1
2 1203 SELECT PROID FROM PROJECT WHERE 1=1 CATEGORY=3401 AND USER_ID=3

这张表有5W多行数据
现在我需要查询这张表，并且把 SQL_TEXT执行为具体的值，这一步不知道如何弄了， SQL函数不能用动态SQL，所以函数用不了，存储过程的返回值不能做为查询的一列查来查询（select a,b,c,(exec proc) from A）没招了，另外不要用游标，目前就是用的游标执行40分钟才出来结果，这个性能领导不满意。求教在线等

...全文

180 15 点赞打赏收藏举报

写回复

15 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

me_child 2015-09-25

引用 6 楼 yangb0803 的回复:

[quote=引用 5 楼 me_child 的回复:] 问题是每一列的查询条件都不一样，唯一一样的是查询的结果只有一个字符串

我知道查询条件不同。我的意思是，你每一个查询语句的值，是不是一定的? 比如： SELECT PROID FROM PROJECT WHERE 1=1 AND CATEGORY=3301 AND USER_ID=1 这个语句执行查询后，获取到的值，是不是固定的？如果是个固定值，那么，我们可以通过游标等方式，一次性的对这列数据进行转化。给数据表新添加一列 SQL_VALUE，一次性的將SQL_TEXT列查询的值保存到新增字段SQL_VALUE。然后给表一个触发器，当纪录在这列修改，或者是新增纪录时，执行触发，將查询到的值写到新列 SQL_VALUE。这样，你就有了该列的值数据了，再对表进行查询时，也就无需再去根据SQL_TEXT内容逐条的来查询，而可以直接就用 SQL_VALUE 的值了。 [/quote] 还是得用游标？那没啥意义啊， 5W行数据游标太坑爹要40多分钟啊现在就是不想用游标，那一列的值永远只查出来一个字符产，不会是别的东西

打赏
举报

回复赞

道玄希言 2015-09-25

引用 5 楼 me_child 的回复:

问题是每一列的查询条件都不一样，唯一一样的是查询的结果只有一个字符串

打赏
举报

回复赞

me_child 2015-09-25

引用 3 楼 yangb0803 的回复:

如果 SELECT PROID FROM PROJECT WHERE 1=1 AND CATEGORY=3301 AND USER_ID=1 这类语句的查询结果不会变化，估计除了游标外，只有添加一个数据列，將那些SQL语句的值，直接保存到表。平时使用时，直接用这列的数据。

问题是每一列的查询条件都不一样，唯一一样的是查询的结果只有一个字符串

打赏
举报

回复赞

me_child 2015-09-25

引用 2 楼 spiritofdragon 的回复:

如果你的每条查询的结果，都能保证是1行1列。那么试试用union all 拼出你的所有查询，再exec。这样速度肯定比游标快。
declare @s varchar(max)='';
with t(id,txt) as (
select 1,'select ''xxx'' ' txt union all
select 2,'select ''yyy'' ' txt union all
select 3,'select ''zzz'' '
)
select @s=@s+' union all select '+convert(varchar(50),t.id)+' id,('+txt+')rst'  from t
set @s=SUBSTRING(@s,11,LEN(@s))
print @s;
exec(@s);
但5w行的数据拼那么长串，是否会遇到什么限制就不知道了。如果有限制，你只能拆成每N个拼个串试试。再把多个结果集，和到一起再输出试试。

你的意思是把5W行记录的SQL都拼起来？

打赏
举报

回复赞

道玄希言 2015-09-25

如果 SELECT PROID FROM PROJECT WHERE 1=1 AND CATEGORY=3301 AND USER_ID=1 这类语句的查询结果不会变化，估计除了游标外，只有添加一个数据列，將那些SQL语句的值，直接保存到表。平时使用时，直接用这列的数据。

打赏
举报

回复赞

spiritofdragon 2015-09-25

如果你的每条查询的结果，都能保证是1行1列。那么试试用union all 拼出你的所有查询，再exec。这样速度肯定比游标快。

declare @s varchar(max)='';
with t(id,txt) as (
select 1,'select ''xxx'' ' txt union all
select 2,'select ''yyy'' ' txt union all
select 3,'select ''zzz'' '
)
select @s=@s+' union all select '+convert(varchar(50),t.id)+' id,('+txt+')rst'  from t
set @s=SUBSTRING(@s,11,LEN(@s))
print @s;
exec(@s);

但5w行的数据拼那么长串，是否会遇到什么限制就不知道了。如果有限制，你只能拆成每N个拼个串试试。再把多个结果集，和到一起再输出试试。

打赏
举报

回复赞

me_child 2015-09-25

人气呢？csdn的人气呢？

打赏
举报

回复赞

spiritofdragon 2015-09-25

bcp 都是1000行，1000行这么导的，你写个分页执行有啥不行...

打赏
举报

回复赞

阳泉酒家小当家 2015-09-25

引用 11 楼 me_child 的回复:

[quote=引用 10 楼 szx1999 的回复:]


--用循环代替游标：
select i=identity(int,1,1),ID,PROID='' into #t from tb

declare @sql varchar(4000),@txt varchar(4000),@i int,@n int
select @n=MAX(i) from #t

while(@i<@n)
begin
	select @txt=a.SQL_TEXT from tb a,#t b where a.ID=b.ID and b.i=@i
	set @sql='update #t set PROID=('+@txt+') where i='+rtrim(@i)
	exec(@sql)
	set @i=@i+1
end

select a.ID,a.TYPE,b.PROID
from tb a,#t b
where a.ID=b.ID

循环和游标的性能差不多吧5W行要循环到啥时候？[/quote] 循环和游标的性能确实差不多的，偶尔性能游标还快一点。我觉得你这个问题，现在不是说用不用动态语句，什么函数，什么存储过程。而是你要执行5w个语句，除非服务器性能超强，否则也会很慢。当然，创建合适的索引，应该能降低查询所需的时间，但也不会说5w条数据，只需要几秒钟的。我觉得能否这样，你新建一个表：表tb 字段 id value 这里的id就是表A的id,而value就是表A中每个语句执行的结果，然后每次如果更新了表A中的sql_text,那么同步更新一下表tb中的value值。这样就不需要每次去查，都做无用功，因为肯定有一些表A中的sqltext是不变化的，就不需要重复去执行了。

打赏
举报

回复赞

spiritofdragon 2015-09-25

引用 4 楼 me_child 的回复:

[quote=引用 2 楼 spiritofdragon 的回复:] 如果你的每条查询的结果，都能保证是1行1列。那么试试用union all 拼出你的所有查询，再exec。这样速度肯定比游标快。
declare @s varchar(max)='';
with t(id,txt) as (
select 1,'select ''xxx'' ' txt union all
select 2,'select ''yyy'' ' txt union all
select 3,'select ''zzz'' '
)
select @s=@s+' union all select '+convert(varchar(50),t.id)+' id,('+txt+')rst'  from t
set @s=SUBSTRING(@s,11,LEN(@s))
print @s;
exec(@s);
但5w行的数据拼那么长串，是否会遇到什么限制就不知道了。如果有限制，你只能拆成每N个拼个串试试。再把多个结果集，和到一起再输出试试。

你的意思是把5W行记录的SQL都拼起来？[/quote] 5w怎么了，这是思路而已。你是一条一条执行，逻辑上肯定没错，但耗的是IO。拼成在一起，省了IO！只是会有长度限制或者别的某些限制。所以需要拆成N段执行。我相信好多人在导生产库的表时本来用insert into select ...一句话就可以搞定的时候，因为耗时长时，怕锁表，超时等原因。有时也得改成insert into select ....where rownum between 1 and 1000 。。。1001 and 2000.....这个分法，用程序就可以拆出来的又不是非得写死。

打赏
举报

回复赞

xqchenxue2 2015-09-25

select SQL_TEXT + ';' from tableName for xml path(''); 一次可以全部查出来,如果感觉太多的话，就分几次取了

打赏
举报

回复赞

me_child 2015-09-25

引用 10 楼 szx1999 的回复:


--用循环代替游标：
select i=identity(int,1,1),ID,PROID='' into #t from tb

declare @sql varchar(4000),@txt varchar(4000),@i int,@n int
select @n=MAX(i) from #t

while(@i<@n)
begin
	select @txt=a.SQL_TEXT from tb a,#t b where a.ID=b.ID and b.i=@i
	set @sql='update #t set PROID=('+@txt+') where i='+rtrim(@i)
	exec(@sql)
	set @i=@i+1
end

select a.ID,a.TYPE,b.PROID
from tb a,#t b
where a.ID=b.ID

循环和游标的性能差不多吧5W行要循环到啥时候？

打赏
举报

回复赞

等不到来世 2015-09-25


--用循环代替游标：
select i=identity(int,1,1),ID,PROID='' into #t from tb

declare @sql varchar(4000),@txt varchar(4000),@i int,@n int
select @n=MAX(i) from #t

while(@i<@n)
begin
	select @txt=a.SQL_TEXT from tb a,#t b where a.ID=b.ID and b.i=@i
	set @sql='update #t set PROID=('+@txt+') where i='+rtrim(@i)
	exec(@sql)
	set @i=@i+1
end

select a.ID,a.TYPE,b.PROID
from tb a,#t b
where a.ID=b.ID

打赏
举报

回复赞

道玄希言 2015-09-25

不然，你一条查询语句执行只要花费 10毫秒， 5W 条纪录就是 500 秒了。实际上，一个查询耗时应该不止这个数了。这还不算其他开消。个人感觉每次取数时及时查询效率太低了。除非你分页，一次只取几条数据。

打赏
举报

回复赞

道玄希言 2015-09-25

引用 7 楼 me_child 的回复:

还是得用游标？那没啥意义啊， 5W行数据游标太坑爹要40多分钟啊现在就是不想用游标，那一列的值永远只查出来一个字符产，不会是别的东西

你沒明白我的意思啊。。。我是说，如果每次你SQL语句查询出的值都是一样的，那就一次性的查出它的值，保存好查询结果到新列SQL_VALUE。以后就不用管那些查询语句了，直接用保存好的值。即：假设 SELECT PROID FROM PROJECT WHERE 1=1 AND CATEGORY=3301 AND USER_ID=1 查询结果为 1 SELECT PROID FROM PROJECT WHERE 1=1 CATEGORY=3401 AND USER_ID=3 查询结果为 2 如果每次执行这些语句，其结果不会变化，也就是说，我之前查询 SELECT PROID FROM PROJECT WHERE 1=1 AND CATEGORY=3301 AND USER_ID=1 这句，值为1, N天以后，查询结果还是 1, 这个结果不会变化，那么我们就可以在数据表上添加一列： SQL_VALUE 將表变成： ID TYPE SQL_VALUE SQL_TEXT 1 1202 1 SELECT PROID FROM PROJECT WHERE 1=1 AND CATEGORY=3301 AND USER_ID=1 2 1203 2 SELECT PROID FROM PROJECT WHERE 1=1 CATEGORY=3401 AND USER_ID=3 新增列后，需要游标或者如2楼说的，拼接出语句，將查询结果写到新列SQL_VALUE。然后，表加上触发器，新增或者 SQL_TEXT 列变化时，將结果写到 SQL_VALUE 。这个游标就只执行一次，写如初始数据到SQL_VALUE，之后表数据有变化，触发器就及时的帮你改正好了。之后，你的查询语句，就不用管SQL_TEXT字段是啥玩意了，就直接 select id, type, sql_value from tbl 了。因为 sql_value 的值，已经就是你 SQL_TEXT 查询出来的了。