Solved

Query Process is Slow Process

Posted on 2011-09-16
33
280 Views
Last Modified: 2012-05-12

Hi
i am using following code to get desired result
i am getting result but its taking long time to process.
and database is not so big, its just 5 MB  MDB file
how can i speed up this process
code and query is
 
If RSL.State = 1 Then RSL.Close
RSL.Open "Select BD.BiltyNo, BD.To, BD.Article, BD.Pvt_Mark AS PM_No, BD.Description, BD.Consignee, BD.Weight, BD.Total, BD.BDate, BD.Consignor, BD.Pymt_mode, BD.CST from Bilty_Detail as BD, Transp_City as TC where CST =  '" & Text1(0).text & "' and BD.G_Name = '" & Text1(3).text & "' or Code = 1 and TC.City_Name = BD.To and TC.Transport_Name = '" & Text1(3).text & "' and IDTNo = 0 group by BD.BiltyNo, BD.To, BD.Article, BD.Pvt_Mark, BD.Description, BD.Consignee, BD.Weight, BD.Total, BD.BDate, BD.Consignor, BD.Pymt_mode, BD.CST order by BD.BiltyNo", con, adOpenKeyset, adLockPessimistic

If RSL.RecordCount > 0 Then
    Set MSHFlexGrid1.DataSource = RSL

Open in new window

0
Comment
Question by:crystal_Tech
  • 17
  • 11
  • 3
  • +2
33 Comments
 
LVL 12

Expert Comment

by:viralypatel
ID: 36550724
This query cannot be refined much further because it is in the simplest form.

Add table indexes on the columns used in where clause. This will make a huge difference.

Read this to know how to add indexes in Access: http://support.microsoft.com/kb/304272
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36550781
why you group them? I dont see any aggregation here...

use distinct/unique and get rid of group by

RSL.Open "Select unique BD.BiltyNo, ...., where ... order by BD.BiltyNo", con, adOpenKeyset, adLockPessimistic
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36550788
ie add "unique" after select and delete "group by BD.BiltyNo, BD.To, BD.Article, BD.Pvt_Mark, BD.Description, BD.Consignee, BD.Weight, BD.Total, BD.BDate, BD.Consignor, BD.Pymt_mode, BD.CST" from query...
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36550794
if unique gives error, use "distinct"
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36550985
HainKurt:
thanks to shorten my query, but its taking same time
0
 
LVL 10

Expert Comment

by:plummet
ID: 36551002
A question: are your two tables supposed to be joined in any way? Currently you are extracting from Bilty_Detail and Transp_City without specifying how they are connected. Generally it's a good idea to use an inner join, or a left join, which connects the tables on a common indexed field value.

Otherwise you're going to end up with a query that takes an age to run, and produces results that you're not expecting.
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36551314


plummet
>>>A question: are your two tables supposed to be joined in any way? <<<<
Relationship created between Bilty_Detail.To and Transp_City.City_Name
and both fields are not indexed
 
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36551630
so, you removed group by and you say no improvement! :) there should be, but maybe not so much...

can you please post the shortened version again...
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36551655
also I did not like that or in the where clause...

it is confusing right now, try to use (..) like:

(this and that) or (this and this and this)

or

this and that and (this or that) and this


Select BD.BiltyNo, BD.To, BD.Article, BD.Pvt_Mark AS PM_No, BD.Description, BD.Consignee, BD.Weight, BD.Total, BD.BDate, BD.Consignor, BD.Pymt_mode, BD.CST 
   from Bilty_Detail as BD, Transp_City as TC 
  where CST =  '" & Text1(0).text & "' and BD.G_Name = '" & Text1(3).text & "' or Code = 1 and TC.City_Name = BD.To and TC.Transport_Name = '" & Text1(3).text & "' and IDTNo = 0 
  order by BD.BiltyNo", con, adOpenKeyset, adLockPessimistic

Open in new window

0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36551751

As your suggestion i removed Group by and  added Distinct
 
RSL.Open "Select distinct(BD.BiltyNo), BD.To, BD.Article, BD.Pvt_Mark AS PM_No, BD.Description, BD.Consignee, BD.Weight, BD.Total, BD.BDate, BD.Consignor, BD.Pymt_mode, BD.CST from Bilty_Detail as BD, Transp_City as TC where CST =  '" & Text1(0).text & "' and BD.G_Name = '" & Text1(3).text & "' or Code = 1 and TC.City_Name = BD.To and TC.Transport_Name = '" & Text1(3).text & "' and IDTNo = 0 order by BD.BiltyNo", con, adOpenKeyset, adLockPessimistic

Open in new window

0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36551770

<<<< (this and that) or (this and this and this) >>>>

little confuse can you please explain me with my query.


and Thanks for quick reply
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36551772
remove parantheses:

RSL.Open "Select distinct(BD.BiltyNo), BD.To, BD.Article, ...
-->
RSL.Open "Select distinct BD.BiltyNo, BD.To, BD.Article, ...
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36551790
this is your where part

CST =  '" & Text1(0).text & "' and BD.G_Name = '" & Text1(3).text & "' or Code = 1 and TC.City_Name = BD.To and TC.Transport_Name = '" & Text1(3).text & "' and IDTNo = 0

and there is an or in the middle... so maybe rewriting this part helps... I cannot predict what to group conditions here

basically you have this:

a and b or c and d and e and f

first use join conditions "TC.City_Name = BD.To" then filtering conditions... are you happy with current result? are they correct? how many result do you get?





0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36551803
ok  removed parantheses
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36551809
yes my current result is very right
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36551929


ok
as you suggested
<<<first use join conditions >>>
i did but result is wrong

where TC.City_Name = BD.To and CST = '" & Text1(0).text & "' and BD.G_Name = '" & Text1(3).text & "' or Code = 1 and TC.Transport_Name = '" & Text1(3).text & "' and IDTNo = 0
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 33

Expert Comment

by:Norie
ID: 36551934
If the 2 tables are linked why isn't there a join in the query?

Without that you'll end up with a cartesian product as the result.

That means if there are X records in one table and Y in the other you will end up with X*Y records returned.

Adding criteria, grouping etc might eventually give you the result you want.
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36551954

<<imnorie:>>
i am getting result i want, but  its takes time to display  result.

0
 
LVL 33

Expert Comment

by:Norie
ID: 36551973
Yes, basically because of what I posted.
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36552088
imnorie
please read my original question.


<<imnorie:>>
i am getting result i want, but  its takes time to display  result.
it means i am already getting my result.
problem is not with the result
problem is it takes time to display result.
0
 
LVL 33

Expert Comment

by:Norie
ID: 36552144
Yes, and the reason it takes so long to get the results is because there is no join in the query.

Adding the criteria, grouping etc is giving the correct results but these things take time.

A join would look something like this:

FROM TC LEFT JOIN BD OB TC.City_Name = BD.To
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36552219

Please Provide exact query as i posted above with your suggestion (Join..)
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 36552255
looks like there is a join "TC.City_Name = BD.To"

where TC.City_Name = BD.To and CST = '" & Text1(0).text & "' and BD.G_Name = '" & Text1(3).text & "' or Code = 1 and TC.Transport_Name = '" & Text1(3).text & "' and IDTNo = 0

but still I could not get the logic here...

post the table definitions of both table and show us what is the relation between these two tables... and also why you use or in where clause?

or try to explain where condition in plain English so we can help... maybe you mean this

where TC.City_Name = BD.To and
(
(CST = '" & Text1(0).text & "' and BD.G_Name = '" & Text1(3).text & "')
or
(Code = 1 and TC.Transport_Name = '" & Text1(3).text & "')
)
and IDTNo = 0

who knows...
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36552444

hey hey..........wow
i thing i got result
HainKurt  O Man...
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36552453
I don't like mixing many "and" & "or" s without using "(...)" It is just confusing...
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36552493
let me test again with different data
speed like formula 1 CAR.. :-D
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36552501
if the result is wrong, try to explain where condition in plain english so we can understand where to put (...) and where to put that or (which creates the issue)
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36552512
Man i am not gonna give even 150 points
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36552555
you know what
you changed whole my  shit logic (sorry )

damn i tried so hard to get that group query, and you destroy it within a minutes.
my whole application was depending on that query,
just the problem was it takes long to display result
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36552560
"shit logic" --> no logic :)
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36552610
yes you can say that "NO Logic"

you deserve 1000 with grade AAA
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36552626

Thanks
i Tested this query with Different  MDB file (Bigger than first one)
and its working like
"ALREADY DATA WAS THERE"
it taking even less than a second
0
 
LVL 1

Author Closing Comment

by:crystal_Tech
ID: 36552633
9470 records

i want specific 2000 record

with my group by query  was taking 2 to 4 miniutes

but with you logic it takes less than seconds

Thanks  A  LOT
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …

707 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

12 Experts available now in Live!

Get 1:1 Help Now