I’m looking for help on being able to speed up my Ranking query. Below are a couple of my Rank formulas. And way below is what I copied from access sql.
How would I clean it up so my query will run faster?
How would I make this a actually sql statement, so that it will run faster?
Or should I look at doing a macro or modules?
I have also attached the database that uses these queries to help better understand what I'm trying to do. Please look at query titled:” qryRank_and_WeightedRank”
If anyone has any advice help or recommendations, please don’t hesitant to tell me. I'm still new and learning and any time I get help or advice it helps me learn. - 3MonthRank: (Select count(*) from GP1_Master_Table_Count_Apps as B where
-
GP1_Master_Table_Count_Apps.[3Month_App_Count] > B.[3Month_App_Count])
- 6MonthRank: (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[6Month_App_Count] > B.[6Month_App_Count])
- 9MonthRank: (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[9Month_App_Count] > B.[9Month_App_Count])
- SELECT GP1_Master_Table_Count_Apps.OMNI_Number, GP1_Master_Table_Count_Apps.[3Month_App_Count], (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[3Month_App_Count] > B.[3Month_App_Count]) AS 3MonthRank, (Select count(*) from GP1_Master_Table_Count_Apps) AS TotalRecords, [3MonthRank]/([TotalRecords]-1)*5 AS 3Month_0to5Rank, CInt([3Month_0to5Rank]*10)/10 AS 3Month_Star_Rating, (Select sum([3Month_App_Count])from GP1_Master_Table_Count_Apps) AS 3MonthGrandTotal, (select min([3Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 3MonthMinTotal, (select max([3Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 3MonthMaxTotal, 5*(([3Month_App_Count]-[3MonthMinTotal])/([3MonthMaxTotal]-[3MonthMinTotal])) AS 3MonthWeightedRank, GP1_Master_Table_Count_Apps.[6Month_App_Count], (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[6Month_App_Count] > B.[6Month_App_Count]) AS 6MonthRank, [6MonthRank]/([TotalRecords]-1)*5 AS 6Month_0to5Rank, CInt([6Month_0to5Rank]*10)/10 AS 6Month_Star_Rating, (Select sum([6Month_App_Count])from GP1_Master_Table_Count_Apps) AS 6MonthGrandTotal, (select min([6Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 6MonthMinTotal, (select max([6Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 6MonthMaxTotal, 5*(([6Month_App_Count]-[6MonthMinTotal])/([6MonthMaxTotal]-[6MonthMinTotal])) AS 6MonthWeightedRank, GP1_Master_Table_Count_Apps.[9Month_App_Count], (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[9Month_App_Count] > B.[9Month_App_Count]) AS 9MonthRank, [9MonthRank]/([TotalRecords]-1)*5 AS 9Month_0to5Rank, CInt([9Month_0to5Rank]*10)/10 AS 9Month_Star_Rating, (Select sum([9Month_App_Count])from GP1_Master_Table_Count_Apps) AS 9MonthGrandTotal, (select min([9Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 9MonthMinTotal, (select max([9Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 9MonthMaxTotal, 5*(([9Month_App_Count]-[9MonthMinTotal])/([9MonthMaxTotal]-[9MonthMinTotal])) AS 9MonthWeightedRank, GP1_Master_Table_Count_Apps.[12Month_App_Count], (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[12Month_App_Count] > B.[12Month_App_Count]) AS 12MonthRank, [12MonthRank]/([TotalRecords]-1)*5 AS 12Month_0to5Rank, CInt([12Month_0to5Rank]*10)/10 AS 12Month_Star_Rating, (Select sum([12Month_App_Count])from GP1_Master_Table_Count_Apps) AS 12MonthGrandTotal, (select min([12Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 12MonthMinTotal, (select max([12Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 12MonthMaxTotal, 5*(([12Month_App_Count]-[12MonthMinTotal])/([12MonthMaxTotal]-[12MonthMinTotal])) AS 12MonthWeightedRank
-
FROM GP1_Master_Table_Count_Apps
;
Thank you for taking helping me with this question
1 1211 NeoPa 32,556
Expert Mod 16PB
I can't help much I'm afraid. This is quite a long and involved piece of SQL (entirely unreadable just as it comes though, so I reproduce it in legible form below).
Ranking SQL, by its very nature, tends to be slow processing. This will always be the case when any (let alone as many as you have) subqueries need to be run over and over again (as must be the case for ranking queries). Once for each record of the master query.
One concept, which you appear to be utilising already anyway, is to refer to calculations already referenced by name so they won't need to be reprocessed. This is usually optimised anyway, but it's not always possible for the optimiser to recognise all situations you can where the dsame values are reused. It also guarantees nono is missed.
I'm afraid the main message is that this isn't such bad SQL, and bearing in mind what it's doing, you need to allow for it running pretty slowly. - SELECT [OMNI_Number]
-
, [3Month_App_Count]
-
, (SELECT Count(*) FROM GP1_Master_Table_Count_Apps AS [b] WHERE ([3Month_App_Count] > B.[3Month_App_Count]) AS 3MonthRank
-
, (SELECT count(*) FROM GP1_Master_Table_Count_Apps) AS TotalRecords
-
, [3MonthRank]/([TotalRecords]-1)*5 AS 3Month_0to5Rank
-
, CInt([3Month_0to5Rank]*10)/10 AS 3Month_Star_Rating
-
, (SELECT Sum([3Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 3MonthGrandTotal
-
, (SELECT min([3Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 3MonthMinTotal
-
, (SELECT max([3Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 3MonthMaxTotal
-
, 5*(([3Month_App_Count]-[3MonthMinTotal])/([3MonthMaxTotal]-[3MonthMinTotal])) AS 3MonthWeightedRank
-
, [6Month_App_Count]
-
, (SELECT count(*) FROM GP1_Master_Table_Count_Apps AS B WHERE [6Month_App_Count] > B.[6Month_App_Count]) AS 6MonthRank
-
, [6MonthRank]/([TotalRecords]-1)*5 AS 6Month_0to5Rank
-
, CInt([6Month_0to5Rank]*10)/10 AS 6Month_Star_Rating
-
, (SELECT Sum([6Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 6MonthGrandTotal
-
, (SELECT min([6Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 6MonthMinTotal
-
, (SELECT max([6Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 6MonthMaxTotal
-
, 5*(([6Month_App_Count]-[6MonthMinTotal])/([6MonthMaxTotal]-[6MonthMinTotal])) AS 6MonthWeightedRank
-
, [9Month_App_Count]
-
, (SELECT count(*) FROM GP1_Master_Table_Count_Apps AS B WHERE [9Month_App_Count] > B.[9Month_App_Count]) AS 9MonthRank
-
, [9MonthRank]/([TotalRecords]-1)*5 AS 9Month_0to5Rank
-
, CInt([9Month_0to5Rank]*10)/10 AS 9Month_Star_Rating
-
, (SELECT Sum([9Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 9MonthGrandTotal
-
, (SELECT min([9Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 9MonthMinTotal
-
, (SELECT max([9Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 9MonthMaxTotal
-
, 5*(([9Month_App_Count]-[9MonthMinTotal])/([9MonthMaxTotal]-[9MonthMinTotal])) AS 9MonthWeightedRank
-
, [12Month_App_Count]
-
, (SELECT count(*) FROM GP1_Master_Table_Count_Apps AS B WHERE [12Month_App_Count] > B.[12Month_App_Count]) AS 12MonthRank
-
, [12MonthRank]/([TotalRecords]-1)*5 AS 12Month_0to5Rank
-
, CInt([12Month_0to5Rank]*10)/10 AS 12Month_Star_Rating
-
, (SELECT Sum([12Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 12MonthGrandTotal
-
, (SELECT min([12Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 12MonthMinTotal
-
, (SELECT max([12Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 12MonthMaxTotal
-
, 5*(([12Month_App_Count]-[12MonthMinTotal])/([12MonthMaxTotal]-[12MonthMinTotal])) AS 12MonthWeightedRank
-
FROM GP1_Master_Table_Count_Apps
Sign in to post your reply or Sign up for a free account.
Similar topics
by: March, Kristopher |
last post by:
----=_NextPart_ST_10_42_47_Saturday_August_23_2003_6287
Content-Type: text/plain;
charset="iso-8859-1"
I'm trying to install DBI perl modules on a SUN 8 box.
I can't seem to find a make...
|
by: Lumpierbritches |
last post by:
Thank you in advance. Is there a way to update Runtime Versions of Access 97,
with just the Queries, Forms, Macros, and Modules, leaving the tables in tact?
Is there a bit of code to do this, or...
|
by: itm |
last post by:
I have to copy a FE/BE Access 2002 database on a regular basis to a CD
to take to another site to use at that site. The data does not have to
be up to the minute at that site.
Is there a simple...
|
by: nezzler |
last post by:
I am currently attempting to write some code which will connect to a
seperate db, identify all the objects (table, queries, forms, macros,
modules etc) and create a clone of these objects in the...
|
by: Jozef |
last post by:
Hello,
I have an Access 2000 database that's somehow gotten corrupt. It's on a
stand alone machine. I started to get an error something to the effect of
"Error Accessing the Network"...
|
by: OhMyGaw |
last post by:
Hello Excel/automation Gurus,
I am working on an application where I have to keep a centralized
database of all macros distributed to user and save the changes back on
a nightly basis back to...
|
by: Onno Hardebol |
last post by:
Hi,
First of all, I'm not a very seasoned programmer. It's just a hobby grown
out of control.
For a project I am working on I need to be able to send and receive e-mail.
I wrote some code for...
|
by: MDMC |
last post by:
Hi all,
I'm trying to install two extensions into my php configuration;
pdo and pdo_mysql
I have downloaded the following packages;
http://pecl.php.net/get/PDO-1.0.3.tgz
and...
|
by: bhood2 |
last post by:
I've come to the conclusion that posting about Embedded Python on the
Python forums is a complete waste of time. I hope I can get some
useful insights here.
I'm looking for some help with...
|
by: ssecorp |
last post by:
Is there a way to view all the modules I have available for import
from within Python?
Like writing in the interpreter:
import.modules
Also, is there anything like Cpan for Python?
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |