脑残设计-视图里包含order by和union

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/Skybig1988/article/details/71123556

今天开发找到我说一条SQL执行的特别慢。SQL里面有一张视图,视图单拿出来跑,需要十几分钟才能出结果(从这里基本上可以定位是视图的问题了)。

SQL和 视图的定义 如下

 

WITH 
"VVVVSUM_TABLE0" AS 
    (
    SELECT
        ......
    FROM
        "ANNE_APPS".VVVVSUM_TABLE "VVVVSUM_TABLE" 
    WHERE 
        "VVVVSUM_TABLE"."ORG_L1" = 'T024' AND
        ("VVVVSUM_TABLE"."AS_OF_DATE" = '201703' AND
        "VVVVSUM_TABLE"."DATA_TYPE" = 'B' AND
        'T01' = 'CNY' AND
        "VVVVSUM_TABLE"."CURRENCY_CD" = 'CNY' OR
        'T01' = 'R01' AND
        "VVVVSUM_TABLE"."CURRENCY_CD" <> 'CNY' OR
        'T01' = 'T01' AND
        1 = 1)
    ), 
"XXXXX" AS 
    (
    SELECT
        ......
    FROM
        VVVVSUM_TABLE0
    ), 
"ORG4" AS 
    (

    SELECT
        "DIM_TREE_3"."LEVEL_01_CODE" AS "LEVEL_01_CODE", 
        "DIM_TREE_3"."LEVEL_02_CODE" AS "LEVEL_02_CODE", 
        "DIM_TREE_3"."LEAF_CODE" AS "LEAF_CODE"
    FROM
        "ANNE_APPS"."VW_DIM_TREE_3" "DIM_TREE_3" 
    WHERE 
        "DIM_TREE_3"."LEVEL_01_CODE" = 'T024'
    ), 
"XXXX" AS 
    (
    SELECT
        "ORG4"."LEVEL_01_CODE" AS "XXXXX", 
        "ORG4"."LEAF_CODE" AS "XXXXX"
    FROM
        "ORG4"
    )
SELECT
   SUM(XXXXXX)
FROM
    XXX
WHERE ......
    ) 
GROUP BY 
    "XXXX"."XXXXXX"

 

 

CREATE OR REPLACE VIEW VVVVSUM_TABLE AS
SELECT
  ...很多个列...
 from AAAAAAAT.RRR_RRRRRRRTABLE
 union
 SELECT
  ...很多个列...
 from AAAAAAAT.RRR_RRRRRRRTABLE_Y
 order by AS_OF_DATE desc


视图里面的表数据量如下:

SELECT COUNT(1) FROM AAAAAAAT.RRR_RRRRRRRTABLE_Y;    ---14754409
SELECT COUNT(1) FROM AAAAAAAT.RRR_RRRRRRRTABLE;         ---10726835

视图里面有order by 这是其一坑

视图里面有union 这是其二坑

union 自带order by作用,这里面居然在结尾加了一个order by ,首先从语意上说这个order by没有任何意义

视图里面有order by本身也是脑残设计,如果确实需要order by可以拿到视图外面去order by

视图里面有order by 。每当访问这个视图的时候,视图都需要先order by【order by的主要消耗来自于table access full】再返回数据。不管外面SQL是否需要order by

比如你需要视图随机返回10行数据where rownum<=10 ,视图内部会先table access full两个表返回2000w行数据进行order by,再返回10行

所以坑不言而喻

同理 :union 比order by还要坑,union 首先需要排序两个大表,然后再去重!!!!

所以到这里我们基本上就有思路了

1.把order by去掉  【任何视图里面都不应该有order by】

2.union改为union all 当然这样改,逻辑就变了。

但是对整个查询没有影响。因为view中union 上下部分别有'A' as DATA_TYPE, 'B' as DATA_TYPE,这种打标可见想对表是单独访问

我把猜想和开发确认,确实如此,页面上有下拉框限制只访问一个表。

修改完后,原SQL 1s响应

总结:视图中有 order by、union、distinct、rownum等需要从业务的角度改写,在设计视图的时候也一定要避免

 

展开阅读全文

图里显示中文的问题

06-22

我目前用的ide是Carbide c++ v1.2。我想做一个中文显示的程序。当然不是在.rls或.loc里面已经写好的中文,而是在视图中直接写中文并可以直接显示出来。可能这样说很不清楚,我举例一下:rn例如:rn const TInt KColorRed=35;rn TRgb colorRed=AKN_LAF_COLOR(95);rn gc.SetPenStyle( CGraphicsContext::ENullPen );rn gc.SetPenColor( colorRed );rn gc.SetBrushStyle( CGraphicsContext::ESolidBrush );rn //Draw Textrn TBuf<16> text(_L("中文显示"));// 1.如果这边写的是英语那是一定显示正常的,但是rn // 中文就会有错误rn TPoint textPoint(2,50);rnrnTZoomFactor devicemap(iCoeEnv->ScreenDevice());rn // Set zoom factor at 200%rn devicemap.SetZoomFactor(TZoomFactor::EZoomOneToOne*1.5);rn TFontSpec fontSpec(_L("Times New Roman"),200);rn // find the nearest font to the specified onern CFont* screenFont;rn devicemap.GetNearestFontInTwips(screenFont,fontSpec);rn // use it for this graphics contextrn gc.UseFont(screenFont);rn gc.DrawText(text,textPoint);rn // discard and release fontrn gc.DiscardFont();rn devicemap.ReleaseFont(screenFont);rnrn如果是这样写的话,那么是无法正常显示中文的,但有时候必需这样写中文。据了解,symbian os 都是使用unicode码的。但是我不知道如何转换才能正常显示。rnrn补充一下:rn 在.rls里面写中文并在文件的最开始加了一句 CHARACTER_SET UTF8(同样,.rss文件也都加上这句)之后把这些文件保存成UTF8 - 无 BOM 格式。这样执行程序就可以正常显示中文了,而在编译器上看到是乱码。但当我在编译器上的:project->properties->info 把 Text file encoding 改成others->UTF-8 格式,那么之前的乱码变成正确的中文字了,而且在执行程序中也可以正常显示。试问,编译器在这种编码格式下,直接在视图下写中文为什么在执行程序下不能正常显示呢?而.rls里的汉字就能正常显示呢?是否CHARACTER_SET UTF8这句就已经把他们转换成Unicode码呢,不过看上去不像才是。rn 论坛

在视图里查询的问题

01-20

视图newsoutquery:rnrnSELECT dbo.ttape.tapeid, dbo.ttape.tapecode, dbo.ttape.program, dbo.ttape.department, rn dbo.ttape.tapetype, dbo.tnewstapedetail.pubdate, dbo.ttapeborrow.borrowmanrnFROM dbo.ttape LEFT OUTER JOINrn dbo.tnewstapedetail ON rn dbo.ttape.tapeid = dbo.tnewstapedetail.tapeid LEFT OUTER JOINrn dbo.ttapeborrow ON dbo.ttape.tapeid = dbo.ttapeborrow.tapeidrnWHERE (dbo.ttapeborrow.returndate IS NULL) AND (dbo.ttape.isnews = 1) AND rn (dbo.ttape.instack = 0)rnrn执行上述查询后可以看到视图里有7条记录。rnrn[code=C#]rn private void queryBtn_Click(object sender, EventArgs e)rn rn if (tapecodeBox.Text != "" && progcomboBox.Text == "请选择")rn rn MessageBox.Show("请先选择栏目名称再输入编号", "警告");rn return;rn rn using (SqlConnection cn = new SqlConnection(mysettings.Localsql))rn rn cn.Open();rn if (isnewscomboBox.Text == "是")rn rn query = "SELECT * from newsoutquery where 1=1 ";rn rn elsern rn query = "SELECT * from progoutquery where 1=1 ";rn rn if (progcomboBox.Text != "请选择")rn rn query = query + "and program = '" + progcomboBox.SelectedText.ToString() + "'";rn rn if (tapecodeBox.Text != "")rn rn query = query + "and tapecode like '%" + tapecodeBox.Text + "%'";rn rn if (tapetypecomboBox.Text != "请选择")rn rn query = query + "and tapetype = '" + tapetypecomboBox.SelectedText.ToString() + "'";rn rn SqlDataAdapter da = new SqlDataAdapter(query, cn);rn DataTable dt = new DataTable();rn da.Fill(dt);rn if (dt.Rows.Count > 0)rn rn bindingSource1.DataSource = dt;rn rn elsern rn MessageBox.Show("查无记录", "警告");rn rn rn rn[/code]rnrn测试时我在progcomboBox选择了数据,并在tapecodeBox输入数据后,进行查询,总是提示查无记录,为什么会这样呢?rn 论坛

sql union order by 排序问题

05-13

当我使用union 以及order by 进行分组排序时,若出现多个union ,那么排序就会有问题,先上代码rnrn以下代码是:先按type分组,然后再排序,最后合并。但当运行时,出现type的顺序是1,1,1,1,1,2,2,2,3,2,2,2,3,但是当我把type1删掉时,显示type的顺序为:2,2,2,2,2,2,3,3.这说明各个分组是相互影响的。请高手帮忙,看问题出现在哪?rnrnrn rn select * from(rn select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,typern from counter rn where type = 1 and year(exportTime) = year(getdate()) rn order by businessNum,billNum) as arn rn unionrn rn select * from(rn select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,typern from counter rn where type = 2 and year(exportTime) = year(getdate()) rn order by businessNum,billNum) as brn rn unionrn select * from(rn select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,typern from counter rn where type = 3 and year(exportTime) = year(getdate()) rn order by businessNum,billNum) as c rn unionrn rn select * from(rn select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,typern from counter rn where type = 4 and year(exportTime) = year(getdate()) rn order by businessNum,billNum)as drn unionrn rn rn select * from(rn select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,typern from counter rn where type = 5 and year(exportTime) = year(getdate()) rn order by businessNum,billNum)as ern rnunionrn rn select * from(rn select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,typern from counter rn where type =6 and year(exportTime) = year(getdate()) rn order by businessNum,billNum) as frnunionrn rn select * from(rn select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,typern from counter rn where type = 7 and year(exportTime) = year(getdate()) rn order by case when arrivalTime is null then 1 else 0 end,businessNum,billNum)as grn unionrnrn select * from(rn select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,typern from counter rn where type = 8 and year(exportTime) = year(getdate()) rn order by case when arrivalTime is null then 1 else 0 end,businessNum,billNum)as hrnrnunionrnrnselect * from(rn select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,typern from counter rn where type = 9 and year(exportTime) = year(getdate()) )as irn unionrnrn select * from(rn select top 100000 id, businessNum,replace( replace( convert(varchar(10),exportTime,102),datename(yyyy,exportTime)+'.','')+datename(dw,exportTime),+'星期','')as exportTime,shipSide,shipOwner,destinationPort,forwarder,addressLation, replace( replace( convert(varchar(20),arrivalTime,102),datename(yyyy,arrivalTime)+'.','')+datename(dw,arrivalTime)+right('0'+datename(hh,arrivalTime),2),'星期','')as arrivalTime,billNum,containerType,containerNum,seal,customFee,trucking,forwarderFee,otherfee,hexiao,closeBill,commodity,customAmount,billAmount,rebateRate,rebateAmount,rebateNum,specialNum,typern from counter rn where type = 10 and year(exportTime) = year(getdate()) )as jrn rnrnrnrnrnrnrn 论坛

没有更多推荐了,返回首页