Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Query Process is Slow Process


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
crystal_Tech
Asked:
crystal_Tech
  • 17
  • 11
  • 3
  • +2
1 Solution
 
viralypatelCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
HainKurtSr. System AnalystCommented:
if unique gives error, use "distinct"
0
 
crystal_TechAuthor Commented:
HainKurt:
thanks to shorten my query, but its taking same time
0
 
plummetCommented:
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
 
crystal_TechAuthor Commented:


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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
crystal_TechAuthor Commented:

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
 
crystal_TechAuthor Commented:

<<<< (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
 
HainKurtSr. System AnalystCommented:
remove parantheses:

RSL.Open "Select distinct(BD.BiltyNo), BD.To, BD.Article, ...
-->
RSL.Open "Select distinct BD.BiltyNo, BD.To, BD.Article, ...
0
 
HainKurtSr. System AnalystCommented:
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
 
crystal_TechAuthor Commented:
ok  removed parantheses
0
 
crystal_TechAuthor Commented:
yes my current result is very right
0
 
crystal_TechAuthor Commented:


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
 
NorieVBA ExpertCommented:
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
 
crystal_TechAuthor Commented:

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

0
 
NorieVBA ExpertCommented:
Yes, basically because of what I posted.
0
 
crystal_TechAuthor Commented:
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
 
NorieVBA ExpertCommented:
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
 
crystal_TechAuthor Commented:

Please Provide exact query as i posted above with your suggestion (Join..)
0
 
HainKurtSr. System AnalystCommented:
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
 
crystal_TechAuthor Commented:

hey hey..........wow
i thing i got result
HainKurt  O Man...
0
 
HainKurtSr. System AnalystCommented:
I don't like mixing many "and" & "or" s without using "(...)" It is just confusing...
0
 
crystal_TechAuthor Commented:
let me test again with different data
speed like formula 1 CAR.. :-D
0
 
HainKurtSr. System AnalystCommented:
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
 
crystal_TechAuthor Commented:
Man i am not gonna give even 150 points
0
 
crystal_TechAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
"shit logic" --> no logic :)
0
 
crystal_TechAuthor Commented:
yes you can say that "NO Logic"

you deserve 1000 with grade AAA
0
 
crystal_TechAuthor Commented:

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
 
crystal_TechAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 17
  • 11
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now