sql - Performance comparison on Join with OR on 2 predicates vs 2 separate joins 1 predicate each -


what's performance impact on using join using 2 predicates or on on clause so:

 select gs.guitartype,gd,guitarcolor  prod.guitars gs  left join prod.guitar_detail gd on (gs.guitarid = gd.guitarid or gs.guitarid  = gd.guitarcatnum)

vs. this:

 select gs.guitartype,gd,guitarcolor  prod.guitars gs  left join prod.guitar_detail gd on gs.guitarid = gd.guitarid   left join prod.guitar_detail gd2 on gs.guitarid  = gd.guitarcatnum

couple caveats: have use left join can't use inner. i've ran both of queries , latter performs better.

also question, 2nd won't return more rows right? because they're both being joined on same table, should both preserve gs table right?

in first query have match twice? or why perform different second?

let me answer in reversed order.

also question, 2nd won't return more rows right? because they're both being joined on same table, should both preserve gs table right?

the queries different (the difference being in how nulls treated), , different execution times should expected. boils down how gd.guitarid , gd.guitarcatnum used.

a) if gd.guitarid set , gd.guitarcatnum null, queries return same data.
b) if gd.guitarid set , gd.guitarcatnum contains same value gd.guitarid, second query return duplicate rows.
c) if gd.guitarid null , gd.guitarcatnum set, queries return same number of rows, gd.guitarcolor returned null.

now, assuming case a), execution plans this:

case 1)

select    gs.guitartype,   gd.guitarcolor     guitars gs    left join guitar_detail gd    on (gs.guitarid = gd.guitarid or        gs.guitarid = gd.guitarcatnum)  access plan: -----------     total cost:         18.3602     query degree:       1                rows               return              (   1)               cost                 i/o                 |                 3               >nljoin              (   2)              18.3602                  2           /-----+------\         2               1.5       tbscan           tbscan      (   3)           (   4)      8.99536          9.07676          1                1         |                |         2                2   table: db2inst1    table: db2inst1        guitars       guitar_detail        q2               q1

case 2)

select    gs.guitartype,   gd.guitarcolor     guitars gs    left join guitar_detail gd    on gs.guitarid = gd.guitarid    left join guitar_detail gd2    on gs.guitarid = gd.guitarcatnum      total cost:         27.2798     query degree:       1                                 rows                                return                               (   1)                                cost                                  i/o                                  |                                  2                                >nljoin                               (   2)                               27.2798                                   3                   /--------------+---------------\                 2                                  1               hsjoin<                            nljoin              (   3)                             (   6)              18.0326                            9.01796                  2                                  1           /-----+------\                     /-----+------\         2                2                0.5               2       tbscan           tbscan            tbscan           tbscan      (   4)           (   5)            (   7)           (   8)      8.99536          8.99536           0.0226           8.99536          1                1                 0                1         |                |                 |                |         2                2                 1                2   table: db2inst1    table: db2inst1    tabfnc: sysibm    table: db2inst1     guitar_detail       guitars           genrow        guitar_detail        q2               q1                q4               q6

hope helps.


-

Comments

Post a Comment

Popular posts from this blog

OpenCV OpenCL: Convert Mat to Bitmap in JNI Layer for Android -

there several posts converting mat bitmap using utils.mattobitmap() function. i'm assuming function can called in java layer after importing utils class. i want transfer data memory address pointed uint32_t* bmpcontent; in code below. jniexport void jnicall java_com_nod_nodcv_nodcvactivity_runfilter( jnienv *env, jclass clazz, jobject outbmp, jbytearray indata, jint width, jint height, jint choice, jint filter) { int outsz = width*height; int insz = outsz + outsz/2; androidbitmapinfo bmpinfo; if (androidbitmap_getinfo(env, outbmp, &bmpinfo) < 0) { throwjavaexception(env,"gaussianblur","error retrieving bitmap meta data"); return; } if (bmpinfo.format != android_bitmap_format_rgba_8888) { throwjavaexception(env,"gaussianblur","expecting rgba_8888 format"); return; } uint32_t* bmpcontent; if (androidbitmap_lockpixels(env, outbmp,(void**)&bmpcontent)...
Read more

android - org.xmlpull.v1.XmlPullParserException: expected: START_TAG {http://schemas.xmlsoap.org/soap/envelope/}Envelope -

i trying hit soap api in application login purpose , getting above mentioned error , can 1 tell me going wrong , first time soap apis. here code hit soap :- vector<object> args = new vector<object>(); hashtable<string, object> hashtable = new hashtable<string, object>(); hashtable.put("email", "mss.siddhart28@gmail.com"); hashtable.put("password", "siddharth"); args.add(hashtable); soapserializationenvelope envelope = new soapserializationenvelope( soapenvelope.ver11); envelope.dotnet = true; envelope.xsd = soapserializationenvelope.xsd; envelope.enc = soapserializationenvelope.enc; (new marshalhashtable()).register(envelope); httptransportse androidhttptransport = new httptransportse( "http://tempuri.org/", 15000); androidhttptransport.debug = true; string result = ""; try { soapobject request = new soapobject("http:...
Read more

python - How to remove the Xframe Options header in django? -

i have made page has iframe . inside iframe want show multiple different links article facebook, or news, or youtube video or other possible url. but, due xframe header, unable so. referred following link: https://docs.djangoproject.com/en/1.8/ref/clickjacking/ , django xframeoptionsmiddleware (x-frame-options) - allow iframe client ip but didn't help. my settings.py file's middleware_classes is: middleware_classes = ( 'django.contrib.sessions.middleware.sessionmiddleware', 'django.middleware.common.commonmiddleware', 'django.middleware.csrf.csrfviewmiddleware', 'django.contrib.auth.middleware.authenticationmiddleware', 'django.contrib.auth.middleware.sessionauthenticationmiddleware', 'django.contrib.messages.middleware.messagemiddleware', 'django.middleware.clickjacking.xframeoptionsmiddleware', ) from http://django-secure.readthedocs.org/en/latest/middleware.html , found using ...
Read more