sql - Optimize MS Access Double SubQuery -

-

this ms access query i'm building slow. testing 1 month of data (one table driving query 28,577 records, 36 columns).

here trying accomplish:

i want return count of unique vehicles model sold in specific transaction type (this database not normalized, , built excel sheet loaded table).

the first subquery exists because understanding in access cannot use distinct statement aggregate function. needed build subquery handle count(distinct vin).

the second subquery believe culprit. each vin may have many entries. example vehicle might have been sold using 1 of transaction categories counting, cancelled, , resold 1 of transaction categories don't want count. produce 3 records vin:

  1. sale transaction category thought wanted count
  2. cancel of first sale
  3. re-sold a transaction category don't want count

the 2nd subquery checks see if vin has "cancelled sale" record , not include first sale in count.

hopefully explained well, , can offer me potential solution speed query.

thanks

query

parameters [enter sales month start date] datetime, [enter sales month end date]  datetime;   select dstnct_count.trans      trans,         dstnct_count.mdl        model,         count(dstnct_count.cnt) vin_count    (select distinct new_bbss.vin              cnt,                          new_bbss.[model category] mdl,                          new_bbss.[trans category] trans            new_bbss           ( ( ( new_bbss.[trans category] ) not 'individual'                     , ( new_bbss.[trans category] ) not 'corporate'                     , ( new_bbss.[trans category] ) not 'cancel'                     , ( new_bbss.[trans category] ) not 'partners'                     , ( new_bbss.[trans category] ) not 'special'                     , ( new_bbss.[trans category] ) not 'employee'                     , ( new_bbss.[trans category] ) not 'mobile'                     , ( new_bbss.[trans category] ) not 'jlr fleet' )                     , ( ( new_bbss.[retailer code] ) 'r*' ) )                     , new_bbss.[trans date] between [enter sales month start date]                                               ,                                                   [enter sales month end date]                     , new_bbss.vin not in(select new_bbss.vin                                               new_bbss                                              new_bbss.[trans category]                                                'cancelvip*'                                                 or new_bbss.[trans category]                                                    'canceldealer local*'                                                 or new_bbss.[trans category]                                                    'cancelloaner*'                                                 or new_bbss.[trans category]                                                    'cancelalive*'))        dstnct_count  group  trans, mdl;

msaccess supposed slower on not in on not exists. don't know whether true, can try anyway. can move restriction clause having clause, vin in group clause. may reduce times msaccess has up.

select [model category], [trans category], count(*) (   select [model category], [trans category], vin   new_bbss   [trans category] not 'individual'      , [trans category] not 'corporate'      , [trans category] not 'cancel'      , [trans category] not 'partners'      , [trans category] not 'special'      , [trans category] not 'employee'      , [trans category] not 'mobile'      , [trans category] not 'jlr fleet'      , [retailer code] 'r*'     , [trans date] between [enter sales month start date]                           , [enter sales month end date]    group [model category], [trans category], vin   having not exists   (     select *     new_bbss unwanted     unwanted.vin = new_bbss.vin     ,       (    unwanted.[trans category] 'cancelvip*'        or unwanted.[trans category] 'canceldealer local*'        or unwanted.[trans category] 'cancelloaner*'        or unwanted.[trans category] 'cancelalive*'     )   ) ) matches group [model category], [trans category];

btw: there should index on vin, msaccess can records quickly.


Comments

Post a Comment

Popular posts from this blog

Java 3D LWJGL collision -

-
i making 3d java game lwjgl library, , wondering how add collision detection, player not go through models. i using obj models. here objloader class, loads models: package renderengine; import java.io.bufferedreader; import java.io.file; import java.io.filenotfoundexception; import java.io.filereader; import java.util.arraylist; import java.util.list; import models.rawmodel; import org.lwjgl.util.vector.vector2f; import org.lwjgl.util.vector.vector3f; public class objloader { public static rawmodel loadobjmodel(string filename, loader loader){ filereader fr = null; try { fr = new filereader(new file("res/"+filename+".obj")); } catch (filenotfoundexception e) { system.err.println("couldn't load file!"); e.printstacktrace(); } bufferedreader reader = new bufferedreader(fr); string line; list<vector3f> vertices = new arraylist<vector3f...
Read more

spring - SubProtocolWebSocketHandler - No handlers -

-
i have ugly error during deploying spring app on jboss. 18:11:16,025 error [org.apache.catalina.core.containerbase.[jboss.web].[default-host].[/consumer]] (msc service thread 1-7) exception sending context initialized event listener instance of class org.springframework.web.context.contextloaderlistener: org.springframework.context.applicationcontextexception: failed start bean 'subprotocolwebsockethandler'; nested exception java.lang.illegalargumentexception: no handlers @ org.springframework.context.support.defaultlifecycleprocessor.dostart(defaultlifecycleprocessor.java:176) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor.access$200(defaultlifecycleprocessor.java:51) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor$lifecyclegroup.start(defaultlifecycleprocessor.java:346) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.s...
Read more

methods - python can't use function in submodule -

-
my folder structure this: pythonstuff/ program.py modulea/ __init__.py foo.py bar.py here's code bar.py : def hello(): print("hello, world!") here's code program.py : #!/usr/bin/env python3 modulea import bar bar.hello() i run $ python3 program.py somehow error: file "program.py", line 3, in <module> bar.hello() attributeerror: 'module' object has no attribute 'hello' edit: __init__.py file empty. edit2: after trying realized had bar.py in root directory contained hello() method. bar.py in modulea/ directory empty. add it import os __all__ = [] module in os.listdir(os.path.dirname(__file__)): if module != '__init__.py' , module[-3:] == '.py': __all__.append(module[:-3]) the init .py files required make python treat directories containing packages; done prevent directories common name, such string, unintentionally hiding v...
Read more