My query takes long time to execute

Question

Post reply

My query takes long time to execute

panpaliachanchal

Valued Member

Points: 64
More actions
April 3, 2009 at 3:31 am

#219692

My database consists of 60 tables & 50 views.
One single table contains nearly 6,00,000 records.
When i use Adobd.recordset to fill it takes a long time .
So instead of using Adodb.addnew,Adodb.update I have used Insert,Delete statements.
But I have provided command buttons for Navigation. I don't have Recordset Object For MoveFirst,MoveLast,Previous,Next ........
I have used Sub-Query for this but it also takes a long time .
For E.g :
"Select * from " & tblName & " where Inwno = (Select max(Inwno) from " & tblName & " where Inwsrno = 1 And Inwno <> 0 and Inwno < " & txt(0) & " and inwtype='" & txt(36) & "')and inwtype='" & txt(36) & "'"
Please Sort me out from these problem .

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

AMJ-458987 SSC Eights! Points: 879 More actions · Answer 1

Hi,

Please specify clearly what you want to do?

But you can do the following things -

Create indexes for tables and views.

You can do all your insert/update/delete/Select using SP.

When you fetch records then you can use SqlDataReader instead of DataSet.

MJ:cool:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

panpaliachanchal (4/3/2009)
My database consists of 60 tables & 50 views.
One single table contains nearly 6,00,000 records.
When i use Adobd.recordset to fill it takes a long time .

Why do you want to fill your recordset with this many rows of data?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

panpaliachanchal Valued Member Points: 64 More actions · Answer 3

I need code to navigate data without using Vb6 Adodb.recordset

I have used for navigation code. But It takes a long time.

what is SP. (You can do all your insert/update/delete/Select using SP)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

panpaliachanchal (4/3/2009)
I need code to navigate data without using Vb6 Adodb.recordset
I have used for navigation code. But It takes a long time.
what is SP. (You can do all your insert/update/delete/Select using SP)

How many rows of data do you load into your Vb6 client application from the server?

SP = stored procedure

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

panpaliachanchal Valued Member Points: 64 More actions · Answer 5

I need code to navigate data without using Vb6 Adodb.recordset

I have used for navigation code. But It takes a long time.

I can't used stored procedure because my condition changes for e.g : i use single table for material inward/outward and other.........

I retrieve 6,00,000 records from server to vb6 client

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

panpaliachanchal (4/3/2009)
I need code to navigate data without using Vb6 Adodb.recordset
I have used for navigation code. But It takes a long time.
I can't used stored procedure because my condition changes for e.g : i use single table for material inward/outward and other.........
I retrieve 6,00,000 records from server to vb6 client

You should only retrieve as many rows as you can display in one screen, or at most a few multiples thereof. If a user wants to see the previous 10 (or 20) rows, then you fetch them from the server when they are needed.

A stored procedure returning 10 or 20 rows would be ideal for this, it would only need a couple of parameters - a start point such as a MaterialID, and another parameter to fix an end point. Your Adodb.recordset will populate in the blink of an eye.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

AMJ-458987 SSC Eights! Points: 879 More actions · Answer 7

can u give an example or exact scenario. it will help us to solve your problem in a better way.

MJ:cool: