CODE ALTERNATIVE?

[airforceone]

I have the following details from my Database

TABLE	# OF RECORDS	FIELDS / COLUMNS
tbl_A	5900	50
tbl_B	6000	40
tbl_C	7000	60
tbl_D	8000	14

and here is the summary of the code performing the query, the code works well and accurate my only problem is it takes forever to finish the reports spanning from 2001 to date of this writing...
any idea is highly appreciated to make the performance of the code a bit faster (ie. other union way, using temp Table prior processing, etc....) I believe sharing my Database would help in crafting the code but due to the nature of the records I could not my apology for that...

SQLScript01 = " SELECT
[tbl_A].[REGI NUMBER],{ OTHER FIELDS UNDER tbl_A },
[tbl_B].[REGI NUMBER],{ OTHER FIELDS UNDER tbl_B }, 
[tbl_C].[REGI NUMBER],{ OTHER FIELDS UNDER tbl_C },
[tbl_D].[REGI NUMBER],{ OTHER FIELDS UNDER tbl_D },"

SQLTable = "newREPORT"
SQLFrom = " FROM ((tbl_A LEFT JOIN tbl_B ON tbl_A.[REGI NUMBER] = tbl_B.[REGI NUMBER]) LEFT JOIN tbl_C ON tbl_A.[REGI NUMBER] = tbl_C.[REGI NUMBER]) LEFT JOIN ref_Details ON tbl_A.[REGI NUMBER] = tbl_D.[REGI NUMBER]"
SQLWhere = "WHERE (YEAR(tbl_A.[DATE REGISTERED]) >= " & YEAR_FROM & " AND YEAR(tbl_A.[DATE REGISTERED]) <= " & YEAR_TO & ")"
SQLAnd = " AND UCase([CLASS TYPE]) <> 'VOLUNTARY'"
SQLOrder = " ORDER BY tbl_A.NATURE, tbl_A.[DATE REGISTERED];"

EySQL = SQLScript01 & " INTO " & SQLTable & SQLFrom & SQLWhere & SQLAnd & SQLOrder
DoCmd.RunSQL EySQL

 

[Micron]

Maybe post a pic of your relationships window - if you created relationships. A bunch of outer joins and 40, 50 fields in each table suggests that's your underlying cause - especially when 4 tables all have the same field name. Using functions in queries tends to slow them down as well, albeit you only have 3 or so in there. But when you add up bottle necks, it's an accumulative things. How many records (appx) in each of those tables?

 

[airforceone]

Maybe post a pic of your relationships window - if you created relationships. A bunch of outer joins and 40, 50 fields in each table suggests that's your underlying cause - especially when 4 tables all have the same field name. Using functions in queries tends to slow them down as well, albeit you only have 3 or so in there. But when you add up bottle necks, it's an accumulative things. How many records (appx) in each of those tables?

I wish I could share relationships but due to sensitive information I could not, please kindly understand.
The 4 tables have different fields as per table is concern, though as specified above they have 50, 40, 60 and 14 fields/columns respectively! as well as the number of records per table (see post 1)
each query/reports need a handful of the fields from 4 tables (but not all fields in one report)
sharing a sample of one of my report

SQLWhere = "WHERE (YEAR(tbl_A.[DATE REGISTERED]) >= " & YEAR_FROM & " AND YEAR(tbl_A.[DATE REGISTERED]) <= " & YEAR_TO & ")"
SQLOrder = " ORDER BY tbl_A.NATURE, tbl_A.[DATE REGISTERED];"
SQLFrom = " FROM ((tbl_A LEFT JOIN tbl_B ON tbl_A.[REGI NUMBER] = tbl_B.[REGI NUMBER]) LEFT JOIN tbl_C ON tbl_A.[REGI NUMBER] = tbl_C.[REGI NUMBER]) LEFT JOIN ref_Details ON tbl_A.[REGI NUMBER] = tbl_D.[REGI NUMBER]"

SQLScript01 = " SELECT [tbl_A].[REGI NUMBER],Format(tbl_A.[DATE REGISTERED],'MMMM dd, yyyy') & ' at ' & Format(tbl_A.[TIME REGISTERED],'Medium Time') AS CONDATE," & _
"[CLASS TYPE],[STAGES OF REG],tbl_A.NATURE,MRS,NARRATIVE,[KINGDOM STATUS],[KINGDOM PROGRESS (OLD)]," & _
"[tbl_B].[REGI NUMBER],Trim(UCase([V1 NAME ] & ' ')) & ' ' & Trim(UCase([V1 LAST ] & ' ')) & ' ' & Trim(UCase([V1 QUALIFIER] & ' ')) & ' y ' & Trim(UCase([V1 MIDDLE ] & ' ')) AS V1FinalName," & _
"[V1 AGE],[V1 SEX],[V1 STATUS],[V1 NAT],[V1 RANK],[tbl_C].[REGI NUMBER]," & _
"Trim(UCase([S1 NAME ] & ' ')) & ' ' & Trim(UCase([S1 LAST ] & ' ')) & ' ' & Trim(UCase([S1 QUALIFIER] & ' ')) & ' y ' & Trim(UCase([S1 MIDDLE ] & ' ')) AS S1FinalName, " & _
"[S1 AGE],[S1 SEX],[S1 STATUS],[S1 NAT],[S1 RANK],HANDLER,LATITUDE,LONGITUDE,tbl_A.[DATE TURNOVER],tbl_A.[DATE REGISTERED],tbl_A.[TIME TURNOVER],tbl_A.[TIME REGISTERED]," & _
"[KINGDOM Solved Type],[Place Of TURNOVER],[Date Filed in Office],[tbl_C].[KINGDOM CASE Number],[INCHARGED],[tbl_C].[S1 KINGDOM Number],OIC,[tbl_C].[Branch],[KINGDOM Status In OFFICE (New)]," & _
"[If RELEASED-Grounds For RELEASED],[Type Of ENTRY (New)],[Date Filed In OFFICE],[KINGDOM Status (New)]," & _
"[tbl_D].[REGI NUMBER],[tbl_D].[NATURE],[ANIMAL INVOLVED],[VEHICLE PLATE NUMBER],[VEHICLE MOTOR NUMBER],[VEHICLE CHASSIS NUMBER],[VEHICLE KIND],[VEHICLE MAKE],[VEHICLE MODEL],[VEHICLE STATUS],[VEHICLE REGISTRATION STATUS],[VEHICLE REGISTRATION NUMBER]"

SQLTable = "newREPORT"
SQLAnd = " AND UCase([CLASS TYPE]) <> 'VOLUNTARY'"
OBeta = True
EySQL = SQLScript01 & " INTO " & SQLTable & SQLFrom & SQLWhere & SQLAnd & SQLOrder
DoCmd.RunSQL EySQL

all variables are created dynamically per users selection.....

 

[Micron]

Slow queries can be due to several issues, any of which can act alone or together to create the problem. Since I can't see the structure I have nothing else to offer that I haven't noted already, except to say you probably have to research this on your own and determine if the suggestions will help you out. The topics will cover table/field structure, lack of/too many indexes, too many functions in the sql, slow network, inadequate hardware, LAN vs WAN and so on. You might try creating nested queries where the first query or so does part(s) of the job. Perhaps try Format function instead of Year or Month and the like, but I suspect those are not the main problem. IIRC, the query plan is available somehow. If you find the tool/method and can understand what you're reading, that might help.

The way you present the problem is like calling up your local garage and asking them to diagnose why your car won't start over the phone, so I think any answers you get will be just as general.
Good luck.

 

[airforceone]

Slow queries can be due to several issues, any of which can act alone or together to create the problem. Since I can't see the structure I have nothing else to offer that I haven't noted already, except to say you probably have to research this on your own and determine if the suggestions will help you out. The topics will cover table/field structure, lack of/too many indexes, too many functions in the sql, slow network, inadequate hardware, LAN vs WAN and so on. You might try creating nested queries where the first query or so does part(s) of the job. Perhaps try Format function instead of Year or Month and the like, but I suspect those are not the main problem. IIRC, the query plan is available somehow. If you find the tool/method and can understand what you're reading, that might help.

The way you present the problem is like calling up your local garage and asking them to diagnose why your car won't start over the phone, so I think any answers you get will be just as general.
Good luck.

thanks mate, appreciate the time.
I've been reading some online tips mostly the structure of query is the main concern, I think I'll focus on that for now.
btw, the app settings is local network in nature as per hardware I think we have a decent one (i7, 16g ram etc...) anyway I still need to find my local garbage man for my GIGO problem! hehe just kidding ciao

 

[james_lankford]

SQLWhere = "WHERE (YEAR(tbl_A.[DATE REGISTERED]) >= " & YEAR_FROM & " AND YEAR(tbl_A.[DATE REGISTERED]) <= " & YEAR_TO & ")"

change to

SQLWhere = "WHERE ( tbl_A.[DATE REGISTERED] >= #1/1/" & YEAR_FROM & "# AND tbl_A.[DATE REGISTERED] <= #12/31/" & YEAR_TO & "# )"

 

[JonXL]

I've read that stored queries run faster, not sure if that's true.

Also, are all your table fields indexed that you're joining/sorting/etc. on?

 

[Micron]

Extensive testing about that here - see link for page six. Colin recently became a MS MVP so take from that what you will.

Speed Comparison Tests - Mendip Data Systems

Database applications for businesses, schools and individuals

www.mendipdatasystems.co.uk

 

[airforceone]

oppps sorry down with a flu
@james_lankford will try to test that this evening, will update you soon thanks mate.
@JonXL yes mate, indexed and normalized
@Micron yes mate seen that and other optimization I could search and applying (slowly) each along my data. (will try to create a similar entry for testing purpose cross finger)