| Author |
Topic |
|
krausr79
Starting Member
19 Posts |
 Posted - 2012-09-20 : 15:15:41
|
| I have a somewhat large database and I'm only concerned with a fraction of the entries. I'm making a monthly usage query that will look at the database once per month:Distinct part numbers from general parts list in july-sept as g left join parts count in jul from general parts list as a on a.part=g.partleft join parts count in aug from general parts list as b on b.part=g.partleft join parts count in sep from general parts list as c on c.part=g.partIn this example I'm looking at 3 months but I may go out to a year. I figure each look at the general parts list (which is several years long) takes kind of a long time. But what if I take the list of distinct parts from just the months I care about as a sub query, and do all of my monthly queries from that condensed list? Would it go faster?If the query engine stores this subquery result and doesn't tamper with it during use, then each time I refer to it, it will have a small cached recordset to do the next join with. Also, this subquery would only have 3 fields as opposed to the 25-odd that the original table does.Does it work this way? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-20 : 16:11:51
|
| That may give you improved performance, but a more desirable way would be to have appropriate indexes on the table. Just by looking at the pseudo code that you have posted, I would think you need indexes on the part column and date column. However, hard to say for sure without seeing the table schema, some sample input and the output you are looking for. |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-09-20 : 16:21:52
|
| Certainly, SQL will cache as much as it can. But you can't guarantee that SQL will cache anything, especially with a large database. In general sub-queries will be slower. You are showing a query with one dominate table and joins to three subordinate (outer join) tables. You can rewrite that to reduce the number of outer joins to one. Which, theoretically, will be faster.As Sunitabeck said, it's hard to give you solid advice without more detail. |
|
|
|
krausr79
Starting Member
19 Posts |
Posted - 2012-09-20 : 16:48:23
|
| I am working off of a database of transactions. The fields I'm interested in are part number, part branch, and date of transaction. Any part may have multiple, 1, or no transactions in a month. My eventual goal is to have each part/branch combo with a yes/no or 0/1 as to whether there were any transactions at all during past months, on a per-month basis. So the fields would be like '1 month ago', 2 month ago'...Part|Branch|1M|2M|3M|...xyz abc y n yxyz azc n n yAyz azc y y nMy current plan is to do a main part/branch list and keep left joining it with queries counting the number of records with dates during the month grouped by part,branch.There is only one table here, but I want to break it up by month. Maybe there's some grouping or partition solution?I am working in excel vba, so I can use that kind of logic to build strings, but no stored procedures or sql variables as far as I know. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-09-20 : 17:55:21
|
| Oh I mis-read your post, yeah you can do that in one query without any joins. If you post sample data in a consumable format, someone can hep you write query real quick. Here are some links that can help you prepare your sample data:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
krausr79
Starting Member
19 Posts |
Posted - 2012-09-21 : 09:20:22
|
| Ok, I've got something acceptably workable. I'm using a for loop to build several case statements into the select. It adds i-1 months to the start date each time for the lower bound and then i months - 1 day for the upper bound. It's a single query and could be tweaked or subqueried to do what I want:sql = "Select illitm, ilmcu, iltrdj"For i = 1 To NumMonths sql = sql & ", case when iltrdj between " & NormalToJulian(DateAdd("m", i - 1, Sdate)) & " and " & NormalToJulian(DateAdd("m", i, Sdate) - 1) & " then 1 else 0 end"Next isql = sql & " From PRDDTA73.F4111"sql = sql & " Where ((ildct='IM' or ildcto in ('SN','SH','SP','SO','SE','SF')) and ILTRDJ between " & NormalToJulian(Sdate) & " and " & NormalToJulian(Fdate) & ")" |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-09-21 : 10:51:02
|
a loop does nothing but slow down the operation. Supply some structure and data like we requested, and one of the super sql guys will give you a very nice query (Hello there sunita  )How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-21 : 11:54:03
|
quote:
Originally posted by DonAtWork
a loop does nothing but slow down the operation. Supply some structure and data like we requested, and one of the super sql guys will give you a very nice query (Hello there sunita )
Where is Visakh when we really need him to write a very nice query!?!  |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-09-21 : 12:54:37
|
Here is the basic syntax wih might get you going. If you need more help, please post DDL and DML per the links provided above.SELECT PartNumbers, COUNT(CASE WHEN DATENAME(MONTH, SaleDate) = 'July' THEN 1 ELSE NULL END) AS JulyCount, COUNT(CASE WHEN DATENAME(MONTH, SaleDate) = 'August' THEN 1 ELSE NULL END) AS AugustCount, COUNT(CASE WHEN DATENAME(MONTH, SaleDate) = 'September' THEN 1 ELSE NULL END) AS SeptemberCountFROM GeneralPartsListWHERE SaleDate >= '2012-07-01' AND SaleDate < '2012-10-01'GROUP BY PartNumbers |
|
|
|
krausr79
Starting Member
19 Posts |
Posted - 2012-09-21 : 14:52:41
|
| The loops are ok because they are only run in Visual Basic to build the SQL statement string. The loops are neccesary because the months are variable; this may be run in any month of the year and the user gets to pick how many months back to look.I am hesitant to supply data because my only window to the database is excel vba. I don't have an 'enterprise manager' or a T-sql window so I can't produce these things on the fly.The project is a success; it runs at a stately 20 second average instead of the 3 minutes it used to take. (Thanks for the helps). I have finalized it and submitted it, so any code you offer from here is purely for future thread readers. |
|
|
|
|
Are subquery results stored/used during queries?