-2

I have WPF application which task is to drag data from Interbase DB. Note, that this DB is located on the remote network device. Also, Firebird ado.net data provider is used.

One of my query looks like:

SELECT 
    T1.ind_st, 
    T2.ttt, 
    T2.tdtdtd, 
    sumr 
FROM   ((SELECT ind_st, 
               Sum(r) AS sumR 
        FROM   (SELECT ind_st, 
                       rrr AS r 
                FROM   srok_tel 
                WHERE  date_ch = '23.07.2018 0:00:00' 
                       AND srok_ch = '18' 
                       AND ind_st >= 33049 
                       AND ind_st <= 34717 
                UNION 
                SELECT ind_st, 
                       -rrr AS r 
                FROM   srok_tel 
                WHERE  date_ch = '23.07.2018 0:00:00' 
                       AND srok_ch = '12' 
                       AND ind_st >= 33049 
                       AND ind_st <= 34717 
                UNION 
                SELECT ind_st, 
                       rrr AS r 
                FROM   srok_tel 
                WHERE  date_ch = '24.07.2018 0:00:00' 
                       AND srok_ch IN ( 6, 12 ) 
                       AND ind_st >= 33049 
                       AND ind_st <= 34717) 
        GROUP  BY ind_st) T1 
        JOIN (SELECT ind_st, 
                     ttt, 
                     tdtdtd 
              FROM   srok_tel 
              WHERE  date_ch = '24.07.2018 0:00:00' 
                     AND srok_ch = '12' 
                     AND ind_st >= 33049 
                     AND ind_st <= 34717) T2 
          ON T1.ind_st = T2.ind_st)

Yes, heavy, hard to read at first look and probably written in a wrong way, but my task is to drag all data with one query and I am NOT sql pro.

Target table (SROK_TEL), from with data is selecting, contains aproximately 10^7 rows. Query run time is about 90 seconds, which is significantly more, then I wish to see.

Any suggestions about how to make this query work faster?

UPDATE1: On luisarcher's request I've added a query plan (hope that's exactly what he asked for)

PLAN JOIN (SORT ((T1 SROK_TEL NATURAL)
PLAN (T1 SROK_TEL NATURAL)
PLAN (T1 SROK_TEL NATURAL)), T2 SROK_TEL INDEX (PK_SROK_TEL))
marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
Eugene
  • 23
  • 5

1 Answers1

0

I've had an issue like yours not long ago, so I'll share some tips that apply to your situation:

1) If you don't mind having duplicates, you can use UNION ALL instead of UNION. You can see why here

2) Restrict the data you use. This one is important; I got about 90% of execution time reduced by correctly removing data I don't need from the query (more specific where clauses, not selecting useless data).

3) Check if you can add an index in your table srok_tel.

RegularNormalDayGuy
  • 613
  • 6
  • 17