Hi gurus,
I just started to look at a very slow-running SQL statement
generated by an application (Siebel). I spooled the SQL from the
application, replaced the bind variables by their values, and tuned
from the Query Analyser. But after awhile, I realized that the
statement using bind variables and the same statement using the values
instead of the bind variables often have completely different
execution plans! Is that normal? Can someone tell me how the SQL
Server treats bind variables. Don't worry about being too technical,
I'm an Oracle DBA/developer.
Thanx
Daniel
1  4673 
Hi,
when optimazer looks at the query with bind variables.. in simple terms.. it
'estimates' value of the variable, puts this into query, and then based on
different combinations vs. cost , finds the best query plan. This is all
good under assumption that data is fairly evenly distributed, so are your
variable values when they are being used in your query. Problems appear when
, distribution is havily scewed, data not indexed, or wrong/missing index on
your table(s).
To clean it up, you need to do some logging in your profiler, grab sql, try
to figure out what the query plan really should be, if you can modify sql
great if not, or play with index. Don't give up ... it has to work.
Lucjan
"Daniel Roy" <da************ *@hotmail.com> wrote in message
news:37******** *************** *@posting.googl e.com... Hi gurus,
I just started to look at a very slow-running SQL statement
generated by an application (Siebel). I spooled the SQL from the
application, replaced the bind variables by their values, and tuned
from the Query Analyser. But after awhile, I realized that the
statement using bind variables and the same statement using the values
instead of the bind variables often have completely different
execution plans! Is that normal? Can someone tell me how the SQL
Server treats bind variables. Don't worry about being too technical,
I'm an Oracle DBA/developer.
Thanx
Daniel This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Achille Carette |
last post by:
Hello all,
I noticed a difference in the explain plans between JDBC using bind
variables (PreparedStatement) and SQLPlus for the same query. The query made
through JDBC using bind variables makes a full table scan, while the query
made in SQLPlus, replacing the "?" by string literals uses an index.
(Platform: Oracle 10.1 / Linux RedHat 9 / JDK 1.4.2 / Oracle 10g thin JDBC
driver )
As a common practice is to write and optimize queries...
|
by: serge |
last post by:
I have an SP that is big, huge, 700-800 lines.
I am not an expert but I need to figure out every possible way that
I can improve the performance speed of this SP.
In the next couple of weeks I will work on preparing SQL statements
that will create the tables, insert sample record and run the SP.
I would hope people will look at my SP and give me any hints on how
I can better write the SP.
|
by: Shelby |
last post by:
Hi,
I used System.Windows.Forms.Application.DoEvents() in a loop to handle user
click close button .
Private Sub btnclose_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnclose.Click
Me.Close()
System.Windows.Forms.Application.Exit
End Sub
|
by: atlaste |
last post by:
Hi,
I'm currently developing an application that uses a lot of
computational power, disk access and memory caching (to be more exact:
an information retrieval platform). In these kind of applications the
last thing that remains is bare performance tuning.
So for example, you can do an 'if then else' on a bit like a 'case/
switch', an 'if/then/else' and as a multiplication with a static
buffer. Or, you can do sorting with an inline...
|
by: Medhatithi |
last post by:
Hi,
I have been in several ways benefiited from this site. I would like to share some sql tuning techniques(simple, but effective) with you all.
SQL Tuning Tips
Oracle Tips Session #6
3/31/98
Kathy Gleeson
________________________________________
| | |
by: Jameson.Quinn |
last post by:
I have:
try:
for line in open(myFileName):
count += 1
except IOError:
print "Can't open myfile"
(I know, this is bad, I never close the file, but its just for
illustration). But then I change it to:
|
by: 73k5blazer |
last post by:
Hello again all..
We have a giant application from a giant software vendor that has very
poor SQL.
It's a PLM CAD application, that makes a call to the db for every cad
node in the assembly.
So for an assembly with many parts, from remote locations, the
performance goes out the window.
Locally, it all works fine because network latency is <1ms. But some
remote sites we have (that are growing) latency can be as high as
80-100ms.
|
by: Vinod Sadanandan |
last post by:
A Roadmap To Query Tuning
============================
For each SQL statement, there are different approaches that could be used to retrieve the required data. Optimization is the process of choosing the most efficient way to retrieve this data based upon the evaluation of a number of different criteria. The CBO bases optimization choices on pre-gathered table and index statistics while the RBO makes it's decisions based on a set of ...
|
by: skaushik |
last post by:
Hi all,
I ran an explain plan for a sql query which has some bind variables used in it. The plan shows that index is being used but the execution takes a very long time. Also, in another query the use of bind variables totally discards the usage of indexes and the explain plan also does not show the use of indexes.
Any idea how the optimizer behaves when using bind variables? It would be great if someone can help me out in making sure...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
