Query optimisation

In my application, my module uses the following tables

  1. PUBLIC_APPLICATION
  2. CATEGORY_MASTER
  3. NOTIFICATION_SITE_DETAIL
  4. DIMENSION_MASTER DM
  5. PUBLIC_REGISTRATION
  6. ALLOTMENT_NOTIFICATION.

From the following tables I am retrieving the data

  • Trigger causes subquery returned more than 1 value
  • Questions About Sort Order Using XQuery in SQL Server
  • SQL Join - two foreign keys in the same table reference a primary key
  • How to Terminate or Kill Suspended Task Automatically in SQL Server 2012?
  • Cannot resolve collation conflict for column 4 in SELECT statement
  • To use a Scalar function or Table valued function,in sql server to determine the best
  • SELECT PA.REGISTRATION_NO,PA.APP_ID,PA.NO_OF_ATTEMPTS,CM.CATEGORY_NAME,
    DM.SITE_DIMENSION,PR.BDA_NO,AN.NOTIFY_ID 
     FROM **PUBLIC_APPLICATION PA,CATEGORY_MASTER CM,NOTIFICATION_SITE_DETAIL NSD,DIMENSION_MASTER DM, PUBLIC_REGISTRATION PR,ALLOTMENT_NOTIFICATION AN** 
    WHERE **CM.CATEGORY_ID = PA.CATEGORY_ID AND 
    NSD.NOTIFY_SITE_ID = PR.NOTIFY_SITE_ID AND 
    DM.DIMENSION_ID = NSD.DIMENSION_ID AND 
    PR.REGISTRATION_NO = PA.REGISTRATION_NO AND 
    AN.NOTIFICATION_NO = PA.NOTIFICATION_NO AND
    PR.NOTIFY_SITE_ID = PA.NOTIFY_SITE_ID AND NSD.NOTIFY_ID = AN.NOTIFY_ID AND 
    PA.NOTIFICATION_NO = ?**  LIMIT ?, ?
    

    PUBLIC_APPLICATION & PUBLIC_REGISTRATION have large number of data, nearly i Million records and other tables have around 5000 records.

    If I execute the above the query it takes more than 30 min to get the results, Can any one suggest me to write the effiecient query to get the results within minimum time.

    forgot to specify, I am using the mysql database for this.

  • Localdb in Visual Studio 2015 error 52 (SQL Server)
  • SQL- COUNT all Rows with WHERE condition in all tables in database
  • Most Executed Stored Procedure?
  • Can SQL Server bcp in a file with Unix line endings?
  • Change column equation in SQL
  • Trying to get the sum of distinct values for distinct files for distinct software versions
  • One Solution collect form web for “Query optimisation”

    By using “STRAIGHT_JOIN”, you tell the optimizer to do it as you say so.
    I moved the Notification # as the first WHERE clause so it gets handled first to limit your set. THEN, I set the joins to the other tables. I had this before when querying gov’t data of 15+ million records to join 15+ tables and it took 20+ hours. By adding just the “STRAIGHT_JOIN” to my already well-formed query, it took about 2 hours … again, 15+ million records and join to over 15 tables for child descriptive details.

    SELECT STRAIGHT_JOIN
            PA.REGISTRATION_NO,
            PA.APP_ID,
            PA.NO_OF_ATTEMPTS,
            CM.CATEGORY_NAME, 
            DM.SITE_DIMENSION,
            PR.BDA_NO,
            AN.NOTIFY_ID 
        FROM 
            PUBLIC_APPLICATION PA,
            CATEGORY_MASTER CM,
            NOTIFICATION_SITE_DETAIL NSD,
            DIMENSION_MASTER DM, 
            PUBLIC_REGISTRATION PR,
            ALLOTMENT_NOTIFICATION AN 
        WHERE 
                PA.NOTIFICATION_NO = ? 
            AND PA.CATEGORY_ID = CM.CATEGORY_ID
            AND PA.REGISTRATION_NO = PR.REGISTRATION_NO
            AND PA.NOTIFICATION_NO = AN.NOTIFICATION_NO
            AND PA.NOTIFY_SITE_ID = PR.NOTIFY_SITE_ID 
            AND PR.NOTIFY_SITE_ID = NSD.NOTIFY_SITE_ID
            AND NSD.DIMENSION_ID  = DM.DIMENSION_ID
            AND NSD.NOTIFY_ID = AN.NOTIFY_ID 
        LIMIT 
            ?, ?
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.