superman19
asked on
SQL's problem with JSP pages...
Hi, I have this funny problem due to the DB design. In fact I think i don't have much choice other than design in this way.
P_code Filter A Fliter B Filter C Filter D Filter F
A134 11/2/2002 10/5/2003 5/5/2001 5/9/2002 5/3/2003
P8A5 5/8/2001 31/1/2003 18/2/1999 7/4/2001 25/2/2003
.
.
.
.
I have this kind of design of my database. And this is the problem.
I have a page, to query a expiring date of each filter. Howwould be my SQL statement would be, i mean i need to go through the data horizontally rather than vertically. I mean i need to go through the product's filter one by one to check for the expired date.
If i use this,
SELECT * from Table1 where Filter > ' "+startDate +" '
then my db design should be like this
P_code Filter
A134 11/2/2002
A134 10/5/2003
A134 5/5/2001
A134 5/9/2002
.
.
.
.
Which i dont want....
Can you help?
Thank You.
P_code Filter A Fliter B Filter C Filter D Filter F
A134 11/2/2002 10/5/2003 5/5/2001 5/9/2002 5/3/2003
P8A5 5/8/2001 31/1/2003 18/2/1999 7/4/2001 25/2/2003
.
.
.
.
I have this kind of design of my database. And this is the problem.
I have a page, to query a expiring date of each filter. Howwould be my SQL statement would be, i mean i need to go through the data horizontally rather than vertically. I mean i need to go through the product's filter one by one to check for the expired date.
If i use this,
SELECT * from Table1 where Filter > ' "+startDate +" '
then my db design should be like this
P_code Filter
A134 11/2/2002
A134 10/5/2003
A134 5/5/2001
A134 5/9/2002
.
.
.
.
Which i dont want....
Can you help?
Thank You.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I agree with jarasa that you should revise your table design. Your origianl design in not normalized and the one jarasa suggested is normalized.
If you are stuck with your currect table desgin, then this is the query that you should try:
SELECT p_code, 'Filter A' as filter_name from Table1 where [Filter A] > ?
union
SELECT p_code, 'Filter B' as filter_name from Table1 where [Filter B] > ?
union
SELECT p_code, 'Filter C' as filter_name from Table1 where [Filter C] > ?
union
SELECT p_code, 'Filter D' as filter_name from Table1 where [Filter D] > ?
union
SELECT p_code, 'Filter E' as filter_name from Table1 where [Filter E] > ?
union
SELECT p_code, 'Filter F' as filter_name from Table1 where [Filter F] > ?
I recommend you to use prepared statement for date operation, otherwise you should replace all the '?' with startDate.
let us know if you have further enquires.
If you are stuck with your currect table desgin, then this is the query that you should try:
SELECT p_code, 'Filter A' as filter_name from Table1 where [Filter A] > ?
union
SELECT p_code, 'Filter B' as filter_name from Table1 where [Filter B] > ?
union
SELECT p_code, 'Filter C' as filter_name from Table1 where [Filter C] > ?
union
SELECT p_code, 'Filter D' as filter_name from Table1 where [Filter D] > ?
union
SELECT p_code, 'Filter E' as filter_name from Table1 where [Filter E] > ?
union
SELECT p_code, 'Filter F' as filter_name from Table1 where [Filter F] > ?
I recommend you to use prepared statement for date operation, otherwise you should replace all the '?' with startDate.
let us know if you have further enquires.
ASKER
i am so touch...
thank you for your respond..
jarasa, tq.
>>>SELECT p_code From Table Where ([Filter A] > startDate OR [Filter B] > startDate OR ...... )
This SQL i think should sovled my problem..
**In fact my main concern is the performance issues after i design the db. I am running at a very low availibility resource machine. I need to take the performance as one of my main issues. If i design more table, i afraid that the retriving time is longer. That's why i plan to put everythign into one table. (But is this the best way??-for retrieving only one tbale ?)
But put it into 2 table is a better recommandation rather than one is it??
what do you think?
**
jimmack, tq.
I think your SQL statement is also can be done. TQ. But what do you think for the "**" statement i make??
kennethxu, tq.
Do you think that the normalized table cab give me a better performance. If I am not wrong, that will be longer time for retrive data instead getting the whole information at one shoot...
can you share your opinions on the "**" i make?
thank you for your respond..
jarasa, tq.
>>>SELECT p_code From Table Where ([Filter A] > startDate OR [Filter B] > startDate OR ...... )
This SQL i think should sovled my problem..
**In fact my main concern is the performance issues after i design the db. I am running at a very low availibility resource machine. I need to take the performance as one of my main issues. If i design more table, i afraid that the retriving time is longer. That's why i plan to put everythign into one table. (But is this the best way??-for retrieving only one tbale ?)
But put it into 2 table is a better recommandation rather than one is it??
what do you think?
**
jimmack, tq.
I think your SQL statement is also can be done. TQ. But what do you think for the "**" statement i make??
kennethxu, tq.
Do you think that the normalized table cab give me a better performance. If I am not wrong, that will be longer time for retrive data instead getting the whole information at one shoot...
can you share your opinions on the "**" i make?
ASKER
jarasa.. and another things is the number of p_code i have is a lot. I afraid it'll reach up to 3 thousand. And if I normalized the table into 2, the size will be huge. Imagine each p_code i have 5 filter.. and the Table's size will be a huge number...
but yet, if this is the performance (which i am not sure), i think i will go for your suggestion.
but yet, if this is the performance (which i am not sure), i think i will go for your suggestion.
>> If i design more table, i afraid that the retriving time is longer
the database performance is not measured by number of tables. rather the right design to make sql easiy and fast to access data.
>>I afraid it'll reach up to 3 thousand.
3 thousand is a small number to a database.
the database performance is not measured by number of tables. rather the right design to make sql easiy and fast to access data.
>>I afraid it'll reach up to 3 thousand.
3 thousand is a small number to a database.
ASKER
>>If i design more table, i afraid that the retriving time is longer
ken, what i mean here is, when i have 2 table, and when i perform a join function or some sort like that in order to retrieve 2 table based on one common p_code, is it will slow my resultset down or not??
ken, what i mean here is, when i have 2 table, and when i perform a join function or some sort like that in order to retrieve 2 table based on one common p_code, is it will slow my resultset down or not??
Hi.
I agree with kennethxu it does not matter the number of tables you have but the way you access to them, if you have two tables and have an index on the second one on p_code and expire date, it will be defenetly faster than with just one table, but if you really don't care about on wich filters has p_code expired, then you could use one table, if you have size problems. But I don't think the change will be that huge on size. Consider to code to numeric value the filter field it will reduce the size, and you can still know on wich one has expired.
What Database are you using?
Normally as much WHERE clauses you have worst perfomance you get since SQL has to process all of them one by one.
Javier
I agree with kennethxu it does not matter the number of tables you have but the way you access to them, if you have two tables and have an index on the second one on p_code and expire date, it will be defenetly faster than with just one table, but if you really don't care about on wich filters has p_code expired, then you could use one table, if you have size problems. But I don't think the change will be that huge on size. Consider to code to numeric value the filter field it will reduce the size, and you can still know on wich one has expired.
What Database are you using?
Normally as much WHERE clauses you have worst perfomance you get since SQL has to process all of them one by one.
Javier
ASKER
Javier.... hm.. sound interesting... i think i can convince...to use 2 table rather than one...ha....
i am using Mc Access
Table 1
p_code
A134
P8A5
Table 2
p_code Filter Name Expiry Date
A134 Filter A 11/2/2002
A134 Filter B 10/5/2003
A134 Filter C 7/23/2002
A134 Filter D 1/12/2003
A134 Filter E 12/7/2002
P8A5 Filter A 5/8/2002
P8A5 Filter B 1/18/2003
P8A5 Filter C 7/28/2001
P8A5 Filter D 10/12/2005
P8A5 Filter E 4/7/2004
another things is when i insert the new line.. i think i will have another issues... how can i insert.. my sql...?
ok lah.. i think i have find my solution.
for this problem title... so let me post another question....... for anohter question...
i am using Mc Access
Table 1
p_code
A134
P8A5
Table 2
p_code Filter Name Expiry Date
A134 Filter A 11/2/2002
A134 Filter B 10/5/2003
A134 Filter C 7/23/2002
A134 Filter D 1/12/2003
A134 Filter E 12/7/2002
P8A5 Filter A 5/8/2002
P8A5 Filter B 1/18/2003
P8A5 Filter C 7/28/2001
P8A5 Filter D 10/12/2005
P8A5 Filter E 4/7/2004
another things is when i insert the new line.. i think i will have another issues... how can i insert.. my sql...?
ok lah.. i think i have find my solution.
for this problem title... so let me post another question....... for anohter question...
I agree with the other comments made. If you can, you should probably change your database schema to "normalise" it (as described by jarasa).
I didn't realise that the WHERE clause was inefficient (thanx jarasa ;-)).
I didn't realise that the WHERE clause was inefficient (thanx jarasa ;-)).
My pleasure.
:c))
:c))
How are you using the information?
If you are displaying a table on the page, perhaps with expired filters being shown in red, then your should read in the entire table and iterate through the ResultSet.
For each row in the ResultSet, you should check the value of each date and display the table entry accordingly.
You could minimise the ResultSet by extending your SQL query as follows:
SELECT * from Table1 where FilterA > ' "+startDate +" ' OR FilterB > ' "+startDate+" ' OR FilterC ... etc.
At least this will only return results where at least one of the filters has expired.