java - Hibernate SQL In clause making CPU usage to 100% -


in java application using sql server , hibernate3 ejb. when tried execute select query with in clause, db server cpu usage reaches 100%. when tried run same query in sql management studio, query running without cpu spikes. application server , db server 2 different machines. table has following schema,

create table student_table (        student_id bigint not null identity      , class_id bigint not null      , student_first_name varchar(100) not null      , student_last_name varchar(100)      , roll_no varchar(100) not null      , primary key (student_id)      , constraint uk_studentunique_1 unique  (class_id, roll_no) );

the table contains around 1000k records. query 

select student_id student_table roll_no in ('a101','a102','a103',.....'a250');

in clause contains 250 values, when tried run above query in sql management studio result retrieved within 1 seconds , without cpu spikes. when tried run same query through hibernate cpu spikes reaches 100% around 60 seconds , result retrieved around 60 seconds. hibernate query is,

criteria studentcriteria = session.createcriteria(studentto.class); studentcriteria.add(restrictions.in("rollno", rollnolists)); //rollnolists arraylist contains 250 strings studentcriteria.setprojection(projections.projectionlist().add(projections.property("studentid"))); list<long> studentids = new arraylist<long>(); list<long> results = (arraylist<long>) studentcriteria.list(); if (results != null && results.size() > 0) {    studentids.addall(results); } return studentids;

what problem why so. if same query running through management studio result retrieved without spikes , result retrieved within 1 seconds. solution???

edit1: hibernate generated query is,

select this_.student_id y0_ student_table this_ this_.roll_no in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

edit2: execution plan after indexing roll_no

create index i_student_roll_no on student_table (roll_no)

my execution plan,

the query run console cacheable , that's why response instantaneous. if @ query, you'll see parameters embedded in query, query planner can detect there's no variation , executions go same plan , same cached result.

the query run hibernate, if native query, uses preparedstatement , parameters bind @ query execution time , to quote 1 of best author on indexing:

what has bind parameters?

the shared execution plan caches of db2, oracle , sql server use hash value of literal sql string key cache. cached plans not found if sql contains literal values vary each execution.

place holders (bind parameters) unify statement sql string identical when executed different values—thus, increasing cache-hit rate.

to solve it, need add index on both (roll_no, student_id) columns query becomes index-only scan.

sql server defaults cluster indexes, limit 1 clustered index per table, might want turn table heap table instead , focus on index-only scans.


-

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