none
Understanding performance on a simple query

    질문

  • Hi, trying to understand performance more than anything, below is a simple select of one field (the PK) in a table containing 7.2million records with 72 columns, however this query is just selecting one and still takes 90 seconds to run, maybe this is correct I don't know.

    SELECT OID -- (PK, bigint, not null)
    FROM LZO_PATIENTAPPOINTMENT  -- 7,294,313 records
    
    -- Indexes 
    -- PKC_LZO_PATIENTAPPOINTMENT_OID
    -- IX_LZO_PATIENTAPPOINTMENT_DW_IMPORT_DATE
    -- IDX_LZO_PATIENTAPPOINTMENT_STATUS
    -- IDX_LZO_PATIENTAPPOINTMENT_PATIENTOID
    -- IDX_LZO_PATIENTAPPOINTMENT_OID
    -- IDX_LZO_PATIENTAPPOINTMENT_LZO_PATIENTAPPOINTMENT_CDIOPAPPTMAPPINGCOVER
    -- IDX_LZO_PATIENTAPPOINTMENT_BOOKINGOID

    2018년 5월 18일 금요일 오전 7:56

모든 응답

  • Hi,

    select <fieldset> from table

    without join or where clause, don't require indexes and an index can't improve performances on this case.

    The only possible strategy is "please Sql, scan the entire table and, for each rows, get me the fieldset".

    So, if the entire table can maintained in Ram, the performances can be really good. If not Sql must read a part od data from disks, so flush it, ad read another part from (slow) disks.

    The performances of your query can change dramatically if you have or not Ram fro Sql (And this is pretty true for each query...).

    At the end, Sql must send result to the client and this can be really slow if client is connected via satellite phone in Antarctica.

    marc.

    • 답변으로 제안됨 Visakh16MVP 2018년 5월 18일 금요일 오전 8:59
    2018년 5월 18일 금요일 오전 8:09
  • Assuming your clustered index is on OID, then since OID is the primary key, it is contained in all the other indexes. So when processing the query

    Select OID FROM LZO_PATIENTAPPOINTMENT

    SQL Server will scan the smallest index, then format and return all the OID values. The two biggest costs are A) scanning that index and B) returning the the result to the client.

    You can determine how much time each of those is taking by running

    Select COUNT(*) FROM LZO_PATIENTAPPOINTMENT

    Since that will also do the scan of the smallest index, but only return one row, the time for that query includes only the disk cost, so the remaining time is the cost of returning those 7M+ rows to the client.

    From that you can determine where your performance problem lies, that may get you started on how to improve it.

    Tom

    2018년 5월 18일 금요일 오후 10:00