Solved

SQL's problem with JSP pages...

Posted on 2003-11-18
11
248 Views
Last Modified: 2012-05-04
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.
0
Comment
Question by:superman19
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 6

Accepted Solution

by:
jarasa earned 250 total points
ID: 9771445
Hi Superman19.

I'm not really sure of what you want but if what you need is to know how many p_codes had been expired without caring on wich filter had, just make a select like this:

SELECT p_code From Table Where ([Filter A] > startDate OR [Filter B] > startDate OR ...... )

That should work, but if you pretend to know which filter has expired, then you should change your desing and make two tables, one with the p_codes and another one with the filters something like this.

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

And then make a query like this:

SELECT table1.p_code, table2.[Filter Name] FROM Table1, Table2 Where Table1.p_code=Table2.p_code and Table2.[Expiry Date] > startDate

This will tell you which p_codes and wich filters are expired.

Have Fun
Javier

0
 
LVL 15

Expert Comment

by:jimmack
ID: 9771467
I'll assume that you're stuck with the table definition you've given above.

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.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9771740
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.
0
 

Author Comment

by:superman19
ID: 9775273
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?




0
 

Author Comment

by:superman19
ID: 9775290
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.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 14

Expert Comment

by:kennethxu
ID: 9775913
>> 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.
0
 

Author Comment

by:superman19
ID: 9776958
>>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??
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9776977
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
0
 

Author Comment

by:superman19
ID: 9777263
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...

0
 
LVL 15

Expert Comment

by:jimmack
ID: 9777271
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 ;-)).
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9777294
My pleasure.

:c))
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Owning a franchise can be the dream of a lifetime. It provides a chance for economic growth. You can be as successful as you want.  To make your franchise successful, you need to market it successfully. Here are six of the best marketing strategies …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now