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?
<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?
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_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?
>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?
>> 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.
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
> > 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
> 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'.
>>> 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.
> 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.....
> 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?
> 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_kus...@yahoo.com.sg> 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?
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?
<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...
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...
|
