Changing SQL NOT IN to JOINS -


enter image description here

hello guys,

our aim script insert missing pairs of product - taxcategory in intermediate table (producttaxcategory)

the following script correctly working trying find way optimize it:

insert producttaxcategory     (producttaxcategory_taxcategoryid,producttaxcategory_productid) select      taxcategoryid     ,productid  product pr cross join taxcategory tx pr.productid not in  (     select producttaxcategory_productid     producttaxcategory )  or pr.productid in  (     select producttaxcategory_productid      producttaxcategory )  , tx.taxcategoryid not in  (     select producttaxcategory_taxcategoryid      producttaxcategory      producttaxcategory_productid = pr.productid  )

how can optimize query ?

try (full statement now):

insert producttaxcategory     (producttaxcategory_taxcategoryid,producttaxcategory_productid) select taxcategoryid, productid  product pr cross join taxcategory tx not exists        (select 1 producttaxcategory          producttaxcategory_productid     = pr.productid         ,   producttaxcategory_taxcategoryid = tx.taxcategoryid)

exists (select 1 ... id=...) better alternative in (select id ... ) constructs.


-

Comments

Post a Comment

Popular posts from this blog

java - Solr query version issue: Invalid version or the data in not in 'javabin' format -

as part of recent project, our solr search engine upgraded 5.0.0. program runs client works fine in production/qa, when run on local system, error message: caused by: java.lang.runtimeexception: invalid version or data in not in 'javabin' format @ org.apache.solr.common.util.javabincodec.unmarshal(javabincodec.java:99) this exception apache-solr-solrj-1.4.1.jar, would've thought needs replaced. when check (running) production code, same jar file there. however, java version on production server has been upgraded java version "1.7.0_79". on local system, i'm pointing @ 1.6 jre sts/eclipse. if change 1.7, start getting 51 vs. 50 class version error in different part of program, indicating there's issue converting 1.7, although again doesn't seem problem on production server. i have tested solr server running correctly via web client. returns data in json format. one question have is, mean when says "invalid version or data isn'...
Read more

Hard vs. Soft Water: What's The Difference?

Hard or Soft? HOW AM I SUPPOSED TO KNOW? THEY BOTH LOOK EXACTLY THE SAME! Hey there, Sapna here for Dnews. From the outside, all water is seemingly the same: clear, mostly flavorless, two hydrogens, one oxygen. But once you get past the basics, water is actually pretty versatile. It can be distilled, filtered, tap, bottled, and if you're real fancy, there's water marketed as kosher, organic or kabbalah. But did you know water can also be hard or soft? No, this isn't to describe how the water actually feels. It actually has to do with its mineral content. Water becomes ‘hard' when it has a higher than normal concentration of minerals, primarily calcium and magnesium. Water picks up these minerals from rocks and soil as it flows through rivers, wells or other waterways. Soft water, on the other hand, has a low mineral content, either because it has not yet flowed through rocks or wells - like rain water - or because the rocks it has come in contact with have little to no...
Read more

The Ten Most Livable Cities In The World

Every year, the Economist Intelligence Unit releases its ranking of the “most livable cities” in the world. Therein, they attempt to quantify what exactly makes living in a city better or worse. It generally breaks down into five categories: stability, healthcare, culture and environment, education, and infrastructure. So, what are the world's most livable cities? Well, in fact, the top seven cities actually comprise just three countries, with six countries total in the top ten. Although it is not true everywhere, countries with effective policies tend to see positive effects country-wide. However, many major cities around the world can actually be victims of their own success. Cities like New York City or London are world renown, but also have higher crime rates, overpopulation leading to infrastructure failure, and often much higher costs of living. The cities in this list are able to avoid those pitfalls. The top ranked city in the world is Melbourne Australia, a title it has...
Read more