|
|
You're in the
sql server programming
group:I ve problem with union
sql server programming:
[quoted text, click to view]
>> I want to select data from table whose name is dynamic (variable) << IF @year=2000 SELECT * FROM T2000 ELSE IF @year=2001 SELECT * FROM T2001 ELSE IF @year=2002 SELECT * FROM T2002 ELSE ... << This design flaw is so bad it has a name, like a disease: "Attribute Splitting". Instead of separate tables, you need one table with a "<something>_year" column in it. You then use VIEWs or queries. You probably also split an attribute in the first part of this posting, and are trying to fix it with a UNION [ALL] construct.
Can you use UNION ALL rather than UNION? UNION requires the
performance of a DISTINCT operation, which is significant overhead. If UNION ALL (which returns all rows from both queries without trying to remove duplicate rows) is sufficient the performance improvement may be significant. As for querying tables T2000, T2001, T2002, this appears to be a database design problem. If all the tables are structured the same they should almost certainly be one table. The messy code you have started to create will only get messier and messier as more problems with this design become evident. If the design can be fixed I strongly suggest doing it before going any further. As far as avoiding zerodivide errorsL SELECT CASE WHEN B <> 0 THEN a/b ELSE NULL END FROM T2 Roy Harvey Beacon Falls, CT On Mon, 18 Jun 2007 22:40:50 +0700, "Bpk. Adi Wira Kusuma" [quoted text, click to view] <adi_wira_kusuma@yahoo.com.sg> wrote:
>I use Sql Server 2000. I've structure like it: > >Create table T1( > RID int primary key, > A varchar(8), > ... > ... >) > >Create table T2( > RID int primary key, > A varchar(8), > ... > ... >) > >To make easy to view, so I make a view like it > >Create view VUni as >Select RID, A From T1 >union > >Select RID, A From T2 > >Now, when I execute > >Select * From VUNI Where RID between 15 and 25 > >So the process needs a lot of times alias Very Slow. How to make it best >performance? > >Oh yeach, it's other question, I want to select data from table which its >name is name dimanically (variable) > >IF @YEAR=2000 Select * FROM T2000 >ELSE >IF @YEAR=2001 Select * FROM T2001 >ELSE >IF @YEAR=2002 Select * FROM T2002 >ELSE >... >... > >To make it compact, So how to the code? > >Next question, How to avoid error? I execute like it > >Select a/b from T2 > >seldom the statement is error, because value of filed "a" is 0. I want if >a/b is error calculation, so it give value 0 automatically. Can I do it? >Without I ve to make a function? Or any function (built in) to handle it?
On Jun 18, 11:40 am, "Bpk. Adi Wira Kusuma"
[quoted text, click to view] <adi_wira_kus...@yahoo.com.sg> wrote: > I use Sql Server 2000. I've structure like it: > > Create table T1( > RID int primary key, > A varchar(8), > ... > ... > ) > > Create table T2( > RID int primary key, > A varchar(8), > ... > ... > ) > > To make easy to view, so I make a view like it > > Create view VUni as > Select RID, A From T1 > union > > Select RID, A From T2 > > Now, when I execute > > Select * From VUNI Where RID between 15 and 25 > > So the process needs a lot of times alias Very Slow. How to make it best > performance? UNION will eliminate any rows that are duplicated between the tables, and this will slow things down. Change your view definition to CREATE VIEW VUni AS SELECT RID,A FROM T1 UNION ALL SELECT RID,A FROM T2 [quoted text, click to view] > Oh yeach, it's other question, I want to select data from table which its > name is name dimanically (variable) > > IF @YEAR=2000 Select * FROM T2000 > ELSE > IF @YEAR=2001 Select * FROM T2001 > ELSE > IF @YEAR=2002 Select * FROM T2002 > ELSE > ... > ... > > To make it compact, So how to the code? I will decline to give an answer to this because I think it represents a bad database design. [quoted text, click to view] > Next question, How to avoid error? I execute like it > > Select a/b from T2 > > seldom the statement is error, because value of filed "a" is 0. I want if > a/b is error calculation, so it give value 0 automatically. Can I do it? > Without I ve to make a function? Or any function (built in) to handle it? SELECT CASE WHEN b=0 THEN 0 ELSE a/b END FROM T2
[quoted text, click to view]
> This design flaw is so bad it has a name, like a disease: "Attribute > Splitting". Instead of separate tables, you need one table with a > "<something>_year" column in it. You then use VIEWs or queries. Obviously you've never worked with large volumes of data, or locking contention; if you had you'd know about partitioned views. Your ignorance is so bad it has a name, 'Classroom Coder'. -- Tony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] [quoted text, click to view] "--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1182182711.757253.279490@n60g2000hse.googlegroups.com... >>> I want to select data from table whose name is dynamic (variable) << > > IF @year=2000 SELECT * FROM T2000 > ELSE > IF @year=2001 SELECT * FROM T2001 > ELSE > IF @year=2002 SELECT * FROM T2002 > ELSE > ... > << > > This design flaw is so bad it has a name, like a disease: "Attribute > Splitting". Instead of separate tables, you need one table with a > "<something>_year" column in it. You then use VIEWs or queries. > > You probably also split an attribute in the first part of this > posting, and are trying to fix it with a UNION [ALL] construct. >
[quoted text, click to view]
> IF @YEAR=2000 Select * FROM T2000 > ELSE > IF @YEAR=2001 Select * FROM T2001 > ELSE > IF @YEAR=2002 Select * FROM T2002 > ELSE Look at partitioned views, put a CHECK constraint on the year and the optimiser will do the work for you; it will also make your app simpler because it would just query a view vw_sales..... Tony. -- Tony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] [quoted text, click to view] "Bpk. Adi Wira Kusuma" <adi_wira_kusuma@yahoo.com.sg> wrote in message
news:%23vCm03bsHHA.484@TK2MSFTNGP06.phx.gbl... >I use Sql Server 2000. I've structure like it: > > Create table T1( > RID int primary key, > A varchar(8), > ... > ... > ) > > Create table T2( > RID int primary key, > A varchar(8), > ... > ... > ) > > To make easy to view, so I make a view like it > > Create view VUni as > Select RID, A From T1 > union > > Select RID, A From T2 > > Now, when I execute > > Select * From VUNI Where RID between 15 and 25 > > So the process needs a lot of times alias Very Slow. How to make it best > performance? > > Oh yeach, it's other question, I want to select data from table which its > name is name dimanically (variable) > > IF @YEAR=2000 Select * FROM T2000 > ELSE > IF @YEAR=2001 Select * FROM T2001 > ELSE > IF @YEAR=2002 Select * FROM T2002 > ELSE > ... > ... > > To make it compact, So how to the code? > > Next question, How to avoid error? I execute like it > > Select a/b from T2 > > seldom the statement is error, because value of filed "a" is 0. I want if > a/b is error calculation, so it give value 0 automatically. Can I do it? > Without I ve to make a function? Or any function (built in) to handle it? > >
On Jun 18, 6:51 pm, Hugo Kornelis
[quoted text, click to view] <h...@perFact.REMOVETHIS.info.INVALID> wrote: > On Mon, 18 Jun 2007 15:47:11 -0000, rpresser wrote: > >SELECT CASE WHEN b=0 THEN 0 ELSE a/b END FROM T2 > > Hi rpresser, > > Or the shorter version: > > SELECT COALESCE(a/NULLIF(b,0),0) FROM T2; Well, yours is fewer characters, but it's harder for my brain to parse. :-) I'm sure there's little if any difference in execution speed...
I use Sql Server 2000. I've structure like it:
Create table T1( RID int primary key, A varchar(8), ... ... ) Create table T2( RID int primary key, A varchar(8), ... ... ) To make easy to view, so I make a view like it Create view VUni as Select RID, A From T1 union Select RID, A From T2 Now, when I execute Select * From VUNI Where RID between 15 and 25 So the process needs a lot of times alias Very Slow. How to make it best performance? Oh yeach, it's other question, I want to select data from table which its name is name dimanically (variable) IF @YEAR=2000 Select * FROM T2000 ELSE IF @YEAR=2001 Select * FROM T2001 ELSE IF @YEAR=2002 Select * FROM T2002 ELSE .... .... To make it compact, So how to the code? Next question, How to avoid error? I execute like it Select a/b from T2 seldom the statement is error, because value of filed "a" is 0. I want if a/b is error calculation, so it give value 0 automatically. Can I do it? Without I ve to make a function? Or any function (built in) to handle it?
Thx for your answer. But for my second question, Please answer. I know it is
bad db design. But I wanna know how to make it compact. Just add my programming sql knowledge thx [quoted text, click to view] "rpresser" <rpresser@gmail.com> wrote in message news:1182181631.504169.41660@w5g2000hsg.googlegroups.com... > On Jun 18, 11:40 am, "Bpk. Adi Wira Kusuma" > <adi_wira_kus...@yahoo.com.sg> wrote: > > I use Sql Server 2000. I've structure like it: > > > > Create table T1( > > RID int primary key, > > A varchar(8), > > ... > > ... > > ) > > > > Create table T2( > > RID int primary key, > > A varchar(8), > > ... > > ... > > ) > > > > To make easy to view, so I make a view like it > > > > Create view VUni as > > Select RID, A From T1 > > union > > > > Select RID, A From T2 > > > > Now, when I execute > > > > Select * From VUNI Where RID between 15 and 25 > > > > So the process needs a lot of times alias Very Slow. How to make it best > > performance? > > UNION will eliminate any rows that are duplicated between the tables, > and this will slow things down. Change your view definition to > > CREATE VIEW VUni AS > SELECT RID,A FROM T1 > UNION ALL > SELECT RID,A FROM T2 > > > > Oh yeach, it's other question, I want to select data from table which its > > name is name dimanically (variable) > > > > IF @YEAR=2000 Select * FROM T2000 > > ELSE > > IF @YEAR=2001 Select * FROM T2001 > > ELSE > > IF @YEAR=2002 Select * FROM T2002 > > ELSE > > ... > > ... > > > > To make it compact, So how to the code? > > I will decline to give an answer to this because I think it represents > a bad database design. > > > > Next question, How to avoid error? I execute like it > > > > Select a/b from T2 > > > > seldom the statement is error, because value of filed "a" is 0. I want if > > a/b is error calculation, so it give value 0 automatically. Can I do it? > > Without I ve to make a function? Or any function (built in) to handle it? > > SELECT CASE WHEN b=0 THEN 0 ELSE a/b END FROM T2 > >
hi, ALL........ UNION ALL less make it best performance. it is still slow.
Any other solutions? because if the view for like it Update TB1 set A=(select A from VUni where RID=TB1.RID) So its process is slow. Or other words, How to give value of TB1.A quickly? because the value of TB1.A can be from one of tables (T1 and T2) [quoted text, click to view] "Roy Harvey" <roy_harvey@snet.net> wrote in message news:3cad73t27r63i81sg3ttjhh4hiq68h02hh@4ax.com... > Can you use UNION ALL rather than UNION? UNION requires the > performance of a DISTINCT operation, which is significant overhead. If > UNION ALL (which returns all rows from both queries without trying to > remove duplicate rows) is sufficient the performance improvement may > be significant. > > As for querying tables T2000, T2001, T2002, this appears to be a > database design problem. If all the tables are structured the same > they should almost certainly be one table. The messy code you have > started to create will only get messier and messier as more problems > with this design become evident. If the design can be fixed I > strongly suggest doing it before going any further. > > As far as avoiding zerodivide errorsL > > SELECT CASE WHEN B <> 0 THEN a/b ELSE NULL END > FROM T2 > > Roy Harvey > Beacon Falls, CT > > On Mon, 18 Jun 2007 22:40:50 +0700, "Bpk. Adi Wira Kusuma" > <adi_wira_kusuma@yahoo.com.sg> wrote: > > >I use Sql Server 2000. I've structure like it: > > > >Create table T1( > > RID int primary key, > > A varchar(8), > > ... > > ... > >) > > > >Create table T2( > > RID int primary key, > > A varchar(8), > > ... > > ... > >) > > > >To make easy to view, so I make a view like it > > > >Create view VUni as > >Select RID, A From T1 > >union > > > >Select RID, A From T2 > > > >Now, when I execute > > > >Select * From VUNI Where RID between 15 and 25 > > > >So the process needs a lot of times alias Very Slow. How to make it best > >performance? > > > >Oh yeach, it's other question, I want to select data from table which its > >name is name dimanically (variable) > > > >IF @YEAR=2000 Select * FROM T2000 > >ELSE > >IF @YEAR=2001 Select * FROM T2001 > >ELSE > >IF @YEAR=2002 Select * FROM T2002 > >ELSE > >... > >... > > > >To make it compact, So how to the code? > > > >Next question, How to avoid error? I execute like it > > > >Select a/b from T2 > > > >seldom the statement is error, because value of filed "a" is 0. I want if > >a/b is error calculation, so it give value 0 automatically. Can I do it? > >Without I ve to make a function? Or any function (built in) to handle it? > >
[quoted text, click to view]
On Mon, 18 Jun 2007 15:47:11 -0000, rpresser wrote: >> Next question, How to avoid error? I execute like it >> >> Select a/b from T2 >> >> seldom the statement is error, because value of filed "a" is 0. I want if >> a/b is error calculation, so it give value 0 automatically. Can I do it? >> Without I ve to make a function? Or any function (built in) to handle it? > >SELECT CASE WHEN b=0 THEN 0 ELSE a/b END FROM T2 Hi rpresser, Or the shorter version: SELECT COALESCE(a/NULLIF(b,0),0) FROM T2; -- Hugo Kornelis, SQL Server MVP
[quoted text, click to view]
On Mon, 18 Jun 2007 21:39:07 -0700, rpresser wrote: >On Jun 18, 6:51 pm, Hugo Kornelis ><h...@perFact.REMOVETHIS.info.INVALID> wrote: >> On Mon, 18 Jun 2007 15:47:11 -0000, rpresser wrote: > >> >SELECT CASE WHEN b=0 THEN 0 ELSE a/b END FROM T2 >> >> Hi rpresser, >> >> Or the shorter version: >> >> SELECT COALESCE(a/NULLIF(b,0),0) FROM T2; > >Well, yours is fewer characters, but it's harder for my brain to >parse. :-) I'm sure there's little if any difference in execution >speed... Hi rpresser, Yeah, you're probably right. I still feel that NULLIF is one of the more underrated functions SQL Server offers. Many people don't know it, because it's so little used - and people who do know it tend not to use it because they fear that others might not know it. Death spiral <g> Mayby I should finally try to find some time to write that blog post about NULLIF that has been on my mind for months already... -- Hugo Kornelis, SQL Server MVP
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |