Re: union all
From: Subbaiahd (subbaiahd_at_hotmail.com)
Date: 01/21/05
- Next message: John Shepherd: "Trigger Problem, Please Help"
- Previous message: Aaron [SQL Server MVP]: "Re: Are you able to use a stored procedure to insert into a table"
- In reply to: Steve Kass: "Re: union all"
- Next in thread: Gert-Jan Strik: "Re: union all"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 21 Jan 2005 11:20:11 -0500
Steve,
I can give you table structure and query plans, I have to find out
management for posting data.
I can send this info for only your personal id , cant post in groups.
Let me know if you still want to analyse it more.
Thanks
SUbbu.
"Steve Kass" <skass@drew.edu> wrote in message
news:OraSPp0$EHA.2584@TK2MSFTNGP09.phx.gbl...
> Subbu,
>
> It certainly seems like a bug to me if 8 million rows vanish when you
> add the ORDER BY clause. You could simply open a support case (select
> your product version here:
>
http://support.microsoft.com/select/default.aspx?ln=en-us&x=17&y=9&target=assistance&c1=508&)
> and let the support staff find out what's going on. If this is indeed a
> bug, typically you won't be charged for the case, but I can't give you a
> promise about that.
>
> If you want to pursue this a bit more here in the newsgroups, I'd be
> interested to see more information (and I think Gert-Jan probably would
> too). Here's what would be helpful:
>
> 1. Provide the CREATE TABLE statements for CLAIMFL, CHKREG, CLIENT,
> PROVIDER, and BILLBNO.
> 2. Give the estimated execution plan for the two queries by running the
> script below (with your query inserted) in Query Analyzer and posting
> the results, which will include two pairs or result sets like this one,
> one pair of results for each of the two queries.
> 3. Also helpful would be to see a row or two that you get in the result
> set when the ORDER BY is not present, but not when it is.
>
> StmtText
> ----------------------------------------------------------------------
> select * into #tp from
> ...
>
> StmtText
>
> ----------------------------------------------------------------------
> |--Table Insert(OBJECT:([#tp]), SET:([#tp].[CHKNO]=...
> ...
>
> -- script to run
> SET SHOWPLAN_TEXT ON
> GO
> select * into #tp from
> (your query) x
> ORDER BY "CLNAME", "CLMNO"
>
> GO
> select * into #tp1 from
> (your query) x
> -- ORDER BY "CLNAME", "CLMNO"
> GO
>
> SET SHOWPLAN_TEXT OFF
> GO
>
> -- SK
>
> Subbaiahd wrote:
>
> >Steve,
> >
> >Thanks for you time.
> >I changed datetime value to '20041201' and submitted the query once
again ,
> >no change same discrepency.
> >I recreate indexes every day, so no chance of corrupted indexes, all
columns
> >are on default collation and language.
> >@@version returned as 8.00.760.
> >
> >Here are the queries i run after changes to datetime values.
> >
> >
> >select * into #tp from
> >(SELECT A.CHKNO, A.CLMNO, A.CLMLN, A.CLMSTAT,
> >A.CLMBDT, A.CLACCT, A.BATCHNO, B.CHKDT,
> >"CLIENT"."CLNAME", "CLIENT"."TRMDATE",
> >"BILLBNO"."MAILDT", "BILLBNO"."GENDATE",
> >"PROVIDER"."PRVBUSTYPE", "PROVIDER"."PRVACCT", "PROVIDER"."PPOACCT"
> >
> >FROM CLAIMFL A INNER JOIN CHKREG B ON A.CHKNO = B.CHKNO
> >left outer join "CLIENT" "CLIENT" ON a."CLACCT"="CLIENT"."CLACCT"
> >left outer join "PROVIDER" "PROVIDER" ON a."PRVACCT"="PROVIDER"."PRVACCT"
> >left outer join "BILLBNO" "BILLBNO" ON a."BATCHNO"="BILLBNO"."BATCHNO"
> >WHERE B.CHKDT BETWEEN '20041201' AND '20050101' and
> >("CLIENT"."TRMDATE" IS NULL OR "CLIENT"."TRMDATE">= '20041201')
> >AND "PROVIDER"."PRVBUSTYPE"='2'
> >
> >union all
> >
> >SELECT A.CHKNO, A.CLMNO, A.CLMLN, A.CLMSTAT,
> >A.CLMBDT, A.CLACCT, A.BATCHNO, null CHKDT,
> >"CLIENT"."CLNAME", "CLIENT"."TRMDATE",
> >"BILLBNO"."MAILDT", "BILLBNO"."GENDATE",
> >"PROVIDER"."PRVBUSTYPE", "PROVIDER"."PRVACCT", "PROVIDER"."PPOACCT"
> >
> >FROM CLAIMFL A
> >inner join "CLIENT" "CLIENT" ON a."CLACCT"="CLIENT"."CLACCT"
> >left outer join "PROVIDER" "PROVIDER" ON a."PRVACCT"="PROVIDER"."PRVACCT"
> >left outer join "BILLBNO" "BILLBNO" ON a."BATCHNO"="BILLBNO"."BATCHNO"
> >WHERE a.chkno is null and
> >("CLIENT"."TRMDATE" IS NULL OR "CLIENT"."TRMDATE">= '20041201')
> >AND "PROVIDER"."PRVBUSTYPE"='2' ) x
> >ORDER BY "CLNAME", "CLMNO"
> >--11449930 output
> >go
> >select * into #tp1 from
> >(SELECT A.CHKNO, A.CLMNO, A.CLMLN, A.CLMSTAT,
> >A.CLMBDT, A.CLACCT, A.BATCHNO, B.CHKDT,
> >"CLIENT"."CLNAME", "CLIENT"."TRMDATE",
> >"BILLBNO"."MAILDT", "BILLBNO"."GENDATE",
> >"PROVIDER"."PRVBUSTYPE", "PROVIDER"."PRVACCT", "PROVIDER"."PPOACCT"
> >
> >FROM CLAIMFL A INNER JOIN CHKREG B ON A.CHKNO = B.CHKNO
> >left outer join "CLIENT" "CLIENT" ON a."CLACCT"="CLIENT"."CLACCT"
> >left outer join "PROVIDER" "PROVIDER" ON a."PRVACCT"="PROVIDER"."PRVACCT"
> >left outer join "BILLBNO" "BILLBNO" ON a."BATCHNO"="BILLBNO"."BATCHNO"
> >WHERE B.CHKDT BETWEEN '20041201' AND '20050101' and
> >("CLIENT"."TRMDATE" IS NULL OR "CLIENT"."TRMDATE">= '20041201')
> >AND "PROVIDER"."PRVBUSTYPE"='2'
> >
> >union all
> >
> >SELECT A.CHKNO, A.CLMNO, A.CLMLN, A.CLMSTAT,
> >A.CLMBDT, A.CLACCT, A.BATCHNO, null CHKDT,
> >"CLIENT"."CLNAME", "CLIENT"."TRMDATE",
> >"BILLBNO"."MAILDT", "BILLBNO"."GENDATE",
> >"PROVIDER"."PRVBUSTYPE", "PROVIDER"."PRVACCT", "PROVIDER"."PPOACCT"
> >
> >FROM CLAIMFL A
> >inner join "CLIENT" "CLIENT" ON a."CLACCT"="CLIENT"."CLACCT"
> >left outer join "PROVIDER" "PROVIDER" ON a."PRVACCT"="PROVIDER"."PRVACCT"
> >left outer join "BILLBNO" "BILLBNO" ON a."BATCHNO"="BILLBNO"."BATCHNO"
> >WHERE a.chkno is null and
> >("CLIENT"."TRMDATE" IS NULL OR "CLIENT"."TRMDATE">= '20041201')
> >AND "PROVIDER"."PRVBUSTYPE"='2' ) x
> >--ORDER BY "CLNAME", "CLMNO"
> >--3356419 output
> >
> >Thanks,
> >Subbu.
> >
> >"Steve Kass" <skass@drew.edu> wrote in message
> >news:OjsJPcx$EHA.608@TK2MSFTNGP15.phx.gbl...
> >
> >
> >>Subbu,
> >>
> >> Generally, the UNION ALL query should return the sum of the
> >>number of rows returned by each SELECT in the UNION ALL,
> >>so this could be a bug or a result of corrupted indexes. However,
> >>there is a slim possibility that something is causing a comparison or
> >>a string-to-datetime conversion to be evaluated differently when the
> >>queries are combined with UNION ALL. Perhaps this could occur
> >>if more than one collation or language setting is involved. Are the
> >>column collations all the same, and also the same as the default
> >>collation of the server instance? And are any of the "tables" in
> >>the query actually views that refer to data on another server that
> >>might have different settings? I don't know the rules governing the
> >>interpretation of {ts '2004-12-01 00:00:00'}, but could it be that
> >>this is interpreted as December 1, 2004 sometimes and as January
> >>12, 2004 at other times?
> >>
> >>Try changing {ts '2004-12-01 00:00:00'} to '20041201' and see if
> >>you still observer the discrepancy in rowcounts. Also check to see
> >>if you are up-to-date on service packs by checking SELECT @@version.
> >>It should return 8.00.760 or later (preferably 8.00.818 or later, to
> >>indicate
> >>that the post-sp3 security fixes are installed also).
> >>
> >>Steve Kass
> >>Drew University
> >>
> >>Subbaiahd wrote:
> >>
> >>
> >>
> >>>Any ideas why the result set is different when i used "order by" clause
> >>>
> >>>
> >for
> >
> >
> >>>outer resultset. the query is in my previous postings.
> >>>
> >>>Thanks,
> >>>Subbu.
> >>>
> >>>
> >>>"Subbaiahd" <subbaiahd@hotmail.com> wrote in message
> >>>news:%23SU6g7a$EHA.3908@TK2MSFTNGP12.phx.gbl...
> >>>
> >>>
> >>>
> >>>
> >>>>Gert-Jan,
> >>>>Great job. Thanks for the quick solution.
> >>>>
> >>>>Everything is matching after i remove order by clause .
> >>>> I still didnt follow how order by clause , which mentioned for outer
> >>>>result set (after unioned) made the difference.
> >>>>
> >>>>Regards,
> >>>>Subbu.
> >>>>
> >>>>"Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message
> >>>>news:41ED7FF7.C0830AF4@toomuchspamalready.nl...
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>Subbu,
> >>>>>
> >>>>>How did you check if the result is correct? Your query does not have
an
> >>>>>ORDER BY clause, which means you cannot depend on any order in the
> >>>>>resultset.
> >>>>>
> >>>>>Below are a few queries that you could use to check the result.
> >>>>>
> >>>>>-- Prepare
> >>>>>SELECT * INTO #TP1 FROM (<subquery 1>) p
> >>>>>SELECT * INTO #TP2 FROM (<subquery 2>) q
> >>>>>SELECT * INTO #TP3 FROM (<subquery 1> UNION ALL <subquery 2>) x
> >>>>>
> >>>>>-- check rowcount. Should return identical values
> >>>>>SELECT (SELECT COUNT(*) FROM #TP1)
> >>>>> +(SELECT COUNT(*) FROM #TP2) AS Individuals
> >>>>> ,(SELECT COUNT(*) FROM #TP3) AS Unioned
> >>>>>
> >>>>>-- check all #TP1 present. Should return identical values
> >>>>>SELECT (
> >>>>> SELECT COUNT(*) FROM #TP3 x WHERE EXISTS (
> >>>>> SELECT 1 FROM #TP1 p
> >>>>> WHERE p.CHKNO = x.CHKNO
> >>>>> AND p.CLMNO = x.CLMNO
> >>>>> ...
> >>>>> AND p.qs = x.qs
> >>>>>) AS PresentInUnioned,(SELECT COUNT(*) FROM #TP1) AS Invidual1
> >>>>>
> >>>>>-- check all #TP2 present. Should return identical values
> >>>>>SELECT (
> >>>>> SELECT COUNT(*) FROM #TP3 x WHERE EXISTS (
> >>>>> SELECT 1 FROM #TP2 q
> >>>>> WHERE q.CHKNO = x.CHKNO
> >>>>> AND q.CLMNO = x.CLMNO
> >>>>> ...
> >>>>> AND q.qs = x.qs
> >>>>>) AS PresentInUnioned,(SELECT COUNT(*) FROM #TP2) AS Invidual2
> >>>>>
> >>>>>-- check for garbage. Should return "0 rows selected"
> >>>>>SELECT * FROM #TP3 x WHERE NOT EXISTS (
> >>>>> SELECT 1 FROM #TP1 p
> >>>>> WHERE p.CHKNO = x.CHKNO
> >>>>> AND p.CLMNO = x.CLMNO
> >>>>> ...
> >>>>> AND p.qs = x.qs
> >>>>>) AND NOT EXISTS (
> >>>>> SELECT 1 FROM #TP2 q
> >>>>> WHERE q.CHKNO = x.CHKNO
> >>>>> AND q.CLMNO = x.CLMNO
> >>>>> ...
> >>>>> AND q.qs = x.qs
> >>>>>)
> >>>>>
> >>>>>Please post back if any of these queries do not return the proper
> >>>>>result.
> >>>>>
> >>>>>Hope this helps,
> >>>>>Gert-Jan
> >>>>>
> >>>>>Subbaiahd wrote:
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>here is actual query
> >>>>>>
> >>>>>>select * INTO #TP3 from
> >>>>>>(
> >>>>>>Select * from (SELECT A.CHKNO, A.CLMNO, A.CLMLN, A.CLMSTAT,
> >>>>>>A.CLMBDT, A.CLACCT, A.BATCHNO, B.CHKDT,
> >>>>>>"CLIENT"."CLNAME", "CLIENT"."TRMDATE",
> >>>>>>"BILLBNO"."MAILDT", "BILLBNO"."GENDATE",
> >>>>>>"PROVIDER"."PRVBUSTYPE", "PROVIDER"."PRVACCT", "PROVIDER"."PPOACCT"
,
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>1
> >>>
> >>>
> >>>
> >>>
> >>>>qs
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>>--INTO TEMPDB..TEMPPRESULT
> >>>>>>
> >>>>>>
> >>>>>>FROM CLAIMFL A INNER JOIN CHKREG B ON A.CHKNO = B.CHKNO
> >>>>>
> >>>>>
> >>>>>>right outer join "CLIENT" "CLIENT" ON a."CLACCT"="CLIENT"."CLACCT"
> >>>>>>right outer join "PROVIDER" "PROVIDER" ON
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>a."PRVACCT"="PROVIDER"."PRVACCT"
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>>right outer join "BILLBNO" "BILLBNO" ON
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>a."BATCHNO"="BILLBNO"."BATCHNO"
> >>>
> >>>
> >>>
> >>>
> >>>>>>WHERE B.CHKDT BETWEEN '20041201' AND '20050101' and
> >>>>>>("CLIENT"."TRMDATE" IS NULL OR "CLIENT"."TRMDATE">={ts '2004-12-01
> >>>>>>00:00:00'})
> >>>>>>AND "PROVIDER"."PRVBUSTYPE"='2') p
> >>>>>>
> >>>>>>union ALL
> >>>>>>
> >>>>>>select * from (
> >>>>>>SELECT c.CHKNO, c.CLMNO, c.CLMLN, c.CLMSTAT,
> >>>>>>c.CLMBDT, c.CLACCT, c.BATCHNO, null CHKDT,
> >>>>>>"CLIENT"."CLNAME", "CLIENT"."TRMDATE",
> >>>>>>"BILLBNO"."MAILDT", "BILLBNO"."GENDATE",
> >>>>>>"PROVIDER"."PRVBUSTYPE", "PROVIDER"."PRVACCT", "PROVIDER"."PPOACCT"
,
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>2
> >>>
> >>>
> >>>
> >>>
> >>>>qs
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>>--INTO TEMPDB..TEMPPRESULTnull
> >>>>>>
> >>>>>>
> >>>>>>FROM CLAIMFL c
> >>>>>
> >>>>>
> >>>>>>inner join "CLIENT" "CLIENT" ON c."CLACCT"="CLIENT"."CLACCT"
> >>>>>>right outer join "PROVIDER" "PROVIDER" ON
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>c."PRVACCT"="PROVIDER"."PRVACCT"
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>>right outer join "BILLBNO" "BILLBNO" ON
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>c."BATCHNO"="BILLBNO"."BATCHNO"
> >>>
> >>>
> >>>
> >>>
> >>>>>>WHERE c.chkno is null and
> >>>>>>("CLIENT"."TRMDATE" IS NULL OR "CLIENT"."TRMDATE">={ts '2004-12-01
> >>>>>>00:00:00'})
> >>>>>>AND "PROVIDER"."PRVBUSTYPE"='2') q ) x
> >>>>>>
> >>>>>>ORDER BY "CLNAME", "CLMNO"
> >>>>>>
> >>>>>>Thanks,
> >>>>>>subbu
> >>>>>>
> >>>>>>"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>message
> >>>
> >>>
> >>>
> >>>
> >>>>>>news:e$t2%23yZ$EHA.3236@TK2MSFTNGP15.phx.gbl...
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>Can you post an example that reproduces the problem (on a smaller
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>scale,
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>>>please, 11 million rows are certainly not necessary :) )
> >>>>>>>
> >>>>>>>The number of rows returned by a UNION ALL query should be the sum
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>of
> >>>
> >>>
> >>>
> >>>
> >>>>the
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>>>number of rows in each of the result sets unioned.
> >>>>>>>
> >>>>>>>--
> >>>>>>>Adam Machanic
> >>>>>>>SQL Server MVP
> >>>>>>>http://www.sqljunkies.com/weblog/amachanic
> >>>>>>>--
> >>>>>>>
> >>>>>>>
> >>>>>>>"Subbaiahd" <subbaiahd@hotmail.com> wrote in message
> >>>>>>>news:uw98xwZ$EHA.2316@TK2MSFTNGP15.phx.gbl...
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>>does union all change results?
> >>>>>>>>
> >>>>>>>>I thought it only merges two result sets, but in my case it
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>changed
> >>>
> >>>
> >>>
> >>>
> >>>>the
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>>>>results
> >>>>>>>>
> >>>>>>>>I have a query as mentioned here
> >>>>>>>>select * from
> >>>>>>>>(
> >>>>>>>>(result set 1)
> >>>>>>>>union all
> >>>>>>>>(result set 2) )
> >>>>>>>>order by col1, col22
> >>>>>>>>
> >>>>>>>>result set 1 has a select on multiple tables with inner and outer
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>join ,
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>>>>when it is executed individually it returns 2731372 rows.
> >>>>>>>>result set 2 has a select on (same)multiple tables with inner and
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>outer
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>>>join
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>>, when it is executed individually it returns 590501 rows
> >>>>>>>>
> >>>>>>>>the combined query with union all returns 11 million plus rows.
> >>>>>>>>
> >>>>>>>>I checked rows returned by each result set when joined together
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>with
> >>>
> >>>
> >>>
> >>>
> >>>>>>Union
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>>all, first result set is ok , but the second one( result set 2)
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>is
> >>>
> >>>
> >>>
> >>>
> >>>>>>giving
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>>me false results, it is not properly filtered by where conditions
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>.
> >>>
> >>>
> >>>
> >>>
> >>>>>>>>I hope you understan what i am saying.
> >>>>>>>>any ideas welcome.
> >>>>>>>>
> >>>>>>>>Thanks,
> >>>>>>>>Subbu.
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>>
> >
> >
> >
> >
- Next message: John Shepherd: "Trigger Problem, Please Help"
- Previous message: Aaron [SQL Server MVP]: "Re: Are you able to use a stored procedure to insert into a table"
- In reply to: Steve Kass: "Re: union all"
- Next in thread: Gert-Jan Strik: "Re: union all"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
