matthieu
Posts: 9
|
| Posted: 04/23/2008, 12:51 PM |
|
Hello to you all, geeks and others,
I've done a web-app that access a database of something like 30 fields for each row and more than 2 millions rows...
I have a pagination with 10 elements. So far so good, but everything becomes tricky when I start to move within my data... for example, if I click to go to the second page, or any other page, it will take ages to refresh ! And it won't give me any answers if I ask for the table to be ordered by ID for example...
Do you have any trick that could help me getting around this problem ?
I'm using ASP, a Sybase DB on a dedicated server and my local IIS.
TIA.
|
 |
 |
DonB
|
| Posted: 04/23/2008, 4:54 PM |
|
You're most certainly incurring a table scan (rather than an indexed
lookup), and perhaps even an issue with whatever database not knowing how to
ask for rows selectively (getting page 'n' of a result set) and thus
scanning upwards past the first n*pagesize rows to get the desired rows. I
don't konw if ASP/your databse has the 'LIMIT' facility implemented as does
the PHP/MySQL environment. This aids PHP/CCS apps tremendously.
--
DonB
"matthieu" <matthieu@forum.codecharge> wrote in message
news:6480f933c59062@news.codecharge.com...
> Hello to you all, geeks and others,
>
> I've done a web-app that access a database of something like 30 fields for
> each
> row and more than 2 millions rows...
>
> I have a pagination with 10 elements. So far so good, but everything
> becomes
> tricky when I start to move within my data... for example, if I click to
> go to
> the second page, or any other page, it will take ages to refresh ! And it
> won't
> give me any answers if I ask for the table to be ordered by ID for
> example...
>
> Do you have any trick that could help me getting around this problem ?
>
> TIA.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
ReneS
Posts: 225
|
| Posted: 04/24/2008, 1:10 AM |
|
Hi,
I am using a database with more than 10 million records and 30+ fields, but don't have that problem with grids and/or records. However with reports is a different story. (MSSql2005, ASP, IIS)
Check what DonP said, because I think that there lies your problem.
Rene
|
|
|
|
matthieu
Posts: 9
|
| Posted: 04/24/2008, 1:05 PM |
|
hum, an index concerns... might be right !
But what about when I use the gridrecord to search into the DB the only field having an id = XYZ, I have as an answer :
Error: [DataDirect][ODBC Sybase Wire Protocol driver]Timeout exceeded. (Microsoft OLE DB Provider for ODBC Drivers)
Any idea how not to have this message !!! I don't understand, normally the query should be sent to the DB server which should send back the row...
When I do the same query in RapidSQL, I got the answer in 10s, but I think all the data are cached locally in the application... Not sure about that though.
|
|
|
|
PetrP
Posts: 25
|
| Posted: 04/27/2008, 9:37 PM |
|
I think that DonB is correct.
See http://infohost.nmt.edu/tcc/help/db/sybase/indexes.html for info about indexing a SybaseDB.
|
|
|
|
Benjamin Krajmalnik
|
| Posted: 04/28/2008, 2:17 PM |
|
First of all, make sure you ahve indices for whichever sorts you have on the
procedure.
Additionally, your search criteria is not clear. It is different if it is
looking for an exact match on a single field, and that column is indexed, or
if it is looking for a match anywhgere on the string.
If it is looking for a match anywhere on the string, an index will not help
you here, and depending on the physical layer, you may very well time out.
You may want to increase the timeout settings, but I would first take a look
at that type of search you are doing.
"matthieu" <matthieu@forum.codecharge> wrote in message
news:64810e7f9644f5@news.codecharge.com...
> hum, an index concerns... might be right !
>
> But what about when I use the gridrecord to search into the DB the only
> field
> having an id = XYZ, I have as an answer :
>
> Error: [DataDirect][ODBC Sybase Wire Protocol driver]Timeout
> exceeded.
> (Microsoft OLE DB Provider for ODBC Drivers)
>
> Any idea how not to have this message !!! I don't understand, normally the
> query should be sent to the DB server which should send back the row...
>
> When I do the same query in RapidSQL, I got the answer in 10s, but I think
> all
> the data are cached locally in the application... Not sure about that
> though.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
csierra
Posts: 123
|
| Posted: 05/03/2008, 10:15 AM |
|
It is a bad idea and a bad practice to make any interface to just 'browse' such a huge ammount of data; instead of a grid, make your interface to go to a search page and the search page to the grid so just a few results come out on the rowset.
On the other hand, if OLEDB is returning timeouts, there are specific parameters to be altered within the connection; also get deep deep into the very db structure; indexes and non redundancy, it is hard for me to imagine a 30 field +2 million records db without outer constraints (numeric fields pointing to other fields in other tables) so probably you are dealing with a query wich performance is poor because of db desing.
Hope this helps
_________________
Yes! I Can!!! |
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/04/2008, 4:57 AM |
|
All of the above +
Read up on Sybase EXPLAIN.
Alter your SQL to include the EXPLAIN statement and watch what's actualy going on.
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
|
