sql server - How to improve this SQL? -


my boss sent query , asked replace more efficient version , achieve following objective:

get records package table has @ least 1 record in pass_package_details table.

given sql:

select distinct      pckg.*        pass_package pckg  join      pass_package_details pckg_dtl on (pckg.package_id = pckg_dtl.package_id)       is_active = 1      , '2015/04/22' between date_start , date_end  order      package_name

correct me if i'm wrong, believe query above slow down performance due join method. after reading this, i'm wondering of query achieved boss requirement , why.

my sql :

attempt #1 - using in:

select     pckg.*      pass_package pckg      is_active = 1     , '2015/04/22' between date_start , date_end      , pckg.package_id in (select distinct pckg_dtl.package_id                              pass_package_details pckg_dtl) order      package_name

attempt #2 - using exists:

select      pckg.*      pass_package pckg      is_active = 1     , '2015/04/22' between date_start , date_end      , exists (select pckg_dtl.package_id                  pass_package_details pckg_dtl                 pckg_dtl.package_id = pckg.package_id) order      package_name

hope valuable information experts here!

edit: i'm using sql server management studio. execution time kinda weird. in method, 1st execution take around 90+ ms, 2nd execution 200 ms, 3rd execution 90+ ms. i'm not sure execution time should refer to. same goes exists method.

exists , in equivalent, , both @ best optimized join, , @ worst.. well, not.

you spent few minutes typing entire post, suggest looking @ execution plan instead better use of time. lost credibility @ "i believe" (even before reading rest , seeing you're wrong) instead of showing hard data support each alternative.

edit: note queries aren't equivalent. top 1 (useless) sort , filter (distinct), other 2 don't.


-

Popular posts from this blog

c# - ODP.NET Oracle.ManagedDataAccess causes ORA-12537 network session end of file -

overview i want replace oracle.dataaccess orcale. managed dataaccess, opening connection latter throws ora-12537 network session end of file exception. exception message / stack trace {oracleinternal.network.networkexception (0x000030f9): ora-12537 : netzwerksession: dateiende @ oracleinternal.network.readerstream.read(orabuf ob) @ oracleinternal.ttc.orabufreader.getdatafromnetwork() @ oracleinternal.ttc.orabufreader.read(boolean bignoredata) @ oracleinternal.ttc.marshallingengine.unmarshalub1(boolean bignoredata) @ oracleinternal.ttc.ttcprotocolnegotiation.readresponse()} i trying connect oracle 11g database , not have client installed on local machine. working test application (unmanaged) using oracle.dataaccess works fine . using system; using oracle.dataaccess.client; namespace app.odp.unmanaged { internal class program { private static void main(string[] args) { //dummy connection string...
Read more

matlab - Compression and Decompression of ECG Signal using HUFFMAN ALGORITHM -

i want compress , decompress ecg signal stored text message text file , find out file size, cr,prd ratio,and qs. here code compression , decompression of ecg signal using huffman algorithm. i'm trying find out file size, compression ratio,prd ratio,qs. but error i'm getting the ecg file in text format. %clearing variables , screen enter code here clear all; close all; clc; [file1]=uigetfile('*.txt'); %fprintf('enter ecg file name :%s',file1); p=file1; t=sprintf('%s',p); b1=dlmread([file1]); len=length(b1); lead=input('enter lead number :'); %lead=str2double(lead); col2=b1(1:end,lead); e=fix(sqrt(len)); m=1; i=1:e j=1:e g2(i,j)=fix(col2(m)*1000); %amplifying 1000 m=m+1; end end g3=g2; i=1:e j=1:e ...
Read more

utf 8 - split utf-8 string into bytes in python -

i trying split utf-8 string bytes in python 3. problem is, when use bytearray, byte, encode etc functions array size of element 14 bytes, not 1 byte expected. need split text file sequence of bytes , send them byte after byte using sockets. tried this: infile = open (file, "r") str = infile.read() byte_str = bytes(str, 'utf-8') print("size of byte_str",sys.getsizeof(byte_str[0])) print gives me 14, need 1... suggestion? quoting official documentation : sys.getsizeof(object[, default]) return size of object in bytes. object can type of object. built-in objects return correct results, not have hold true third-party extensions implementation specific. only memory consumption directly attributed object accounted for, not memory consumption of objects refers to. if given, default returned if object not provide means retrieve size. otherwise typeerror raised. getsizeof() calls object’s __sizeof__ method , adds ...
Read more