Weird joins the behavior in sql

This question already has an answer here: Strange / esoteric join syntax 2 answers I recently found old code that uses JOIN JOIN ON ON instead of the more familiar JOIN ON JOIN ON syntax. DECLARE @a TABLE ( val INT ) DECLARE @b TABLE ( val INT ) DECL

Slow SQL, trying to understand what's going on

I have this SQL that started to run very slow on production server ( >20s ). When I run it locally on my laptop on the same DB it runs under 1s. I know that SQL is not optimal but I tried to run EXPLAIN but I don't quite understand the result. SELECT

Index not used in the query. How to improve performance?

I have this query: SELECT * FROM `av_cita` JOIN `av_cita_cstm` ON ( ( `av_cita`.`id` = `av_cita_cstm`.`id_c` ) ) WHERE av_cita.deleted = 0 This query takes over 120 seconds to finish, yet I have added all indexes. When I ask for the execution plan: e

Slow Postgres 9.3 Requests, again

This is a follow-up to the question at Slow Postgres 9.3 queries. The new indexes definitely help. But what we're seeing is sometimes queries are much slower in practice than when we run EXPLAIN ANALYZE. An example is the following, run on the produc

Generating / filling Explain tables in DB2 and using db2exfmt

I'm having a problem while i'm trying to collect explain plan with the db2exfmt tool. Can some body explain me the process of how to use that tool? My requirement is to collect cost of a stored procedure. I have the EXPLAIN tables created in instance

How to retrieve query execution statistics using Oracle DB?

I am new to database. I try to run a simple query on SQL Server 2014 and Oracle 12c. This is the execution plan I get using SQL Server. It contains information about I/O cost and CPU cost in seconds. However I can't find the same information using Or

Confused about the cost of SQL in PL / SQL Developer

Select pn.pn_level_id From phone_number pn Where pn.phone_number='15183773646' Select pn.pn_level_id From phone_number pn Where pn.phone_number=' 15183773646' Do you think they are the same ? No. Basically they are not the same in the PL/SQL Devloper

ANSI_NULLS and QUOTED_IDENTIFIER behavior

I have a view: create view dbo.vMainTable as select MT.* from dbo.Table1 T1 with(index(dbo_Table1_UQ)) inner loop join dbo.MainTable MT with(index(dbo_MainTable_PK)) on T1.ID = MT.Table1_ID go On querying the view, I get the below error if ANSI_NULLS

Postgresql query plan difference

I'm attempting to debug a query which is slow on production, but fast on my development machine. My dev box has a snapshot of the prod database which is only a couple of days old, so the contents of both DBs are roughly the same. The query is: select

Some columns affect the execution plan?

I have a query something like this. It has an execution plan using an index that I expect, up until the amount of data (i.e. the number of characters) returned by the SELECT goes over a boundary. At that point, the plan no longer uses the index and t

Analyze ORACLE EXPLAIN PLAN

Browser is getting hanged while generating jasper report[PDF format] which runs a query whose explain plan is given below. Please help to anlyse the query, is this query taking too much time? We also noticed stuck thread while generating this report.

PostgreSQL: The lower the limit, the slower the query

I have the following query SELECT translation.id FROM "TRANSLATION" translation INNER JOIN "UNIT" unit ON translation.fk_id_unit = unit.id INNER JOIN "DOCUMENT" document ON unit.fk_id_document = document.id WHERE document.fk_

SQL selection field causing a major performance problem

I have a stored procedure that joins in numerous tables and selects fields from them. One of the tables being a temporary table. SELECT a.Field1, a.Field2, b.Field3, b.Field4, c.Field5 FROM table1 a LEFT JOIN #table2 b ON a.Field1 = b.Field1 INNER JO

SQL Server: What will trigger the execution plan?

If I have statement DECLARE @i INT; DECLARE @d NUMERIC(9,3); SET @i = 123; SET @d = @i; SELECT @d; and I include actual execution plan and run this query, I don't get an execution plan. Will the query trigger execution plan only when there is FROM st

Oracle style run tips

When you write rather complex SQL for Oracle, sooner or later you will have to apply the odd execution hint because Oracle can't seem to figure out the "best" execution plan itself. http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/

The index is not used + 2005 sql server

Here is my query Declare @StartDateTime datetime, @EndDateTime datetime Select @StartDateTime = '2010-11-15', @EndDateTime = '2010-11-16' Select PracticeCode, AccountNo, ProcCd, Modifier , ChargeDos, Paid as Amt, CreatedDate, case When Paid > 0 then

Oracle EXECUTE IMMEDIATE changes explain the query plan

I have a stored procedure that I am calling using EXECUTE IMMEDIATE. The issue that I am facing is that the explain plan is different when I call the procedure directly vs when I use EXECUTE IMMEDIATE to call the procedure. This is causing the execut

Stored processes breaking during the night

We are running MS SQL 2005 and we have been experiencing a very peculiar problem the past few days. I have two procs, one that creates an hourly report of data. And another that calls it, puts its results in a temp table, and does some aggregations,

No query plan for the procedure in SQL Server 2005

We have a SQL Server DB with 150-200 stored procs, all of which produce a viewable query plan in sys.dm_exec_query_plan except for one. According to http://msdn.microsoft.com/en-us/library/ms189747.aspx: Under the following conditions, no Showplan ou

Reset SQL Server Execution Plan

I've looked all over for this command....what's the command to reset the SQL Server's execution plan?For clarity.......... Executing sp_recompile will "mark" the given stored procedure for recompilation, which will occur the next time it is exec

Will my index be used if not all columns are used?

I have an index on columns A, B, C, D of table T I have a query that pulls from T with A, B, C in the WHERE clause. Will the index be used or will a separate index be needed that only includes A, B, C?David B is right that you should check the execut

MySQL Query Performance - Huge Time Difference

I have a query that is returning in vastly different amounts of time between 2 datasets. For one set (database A) it returns in a few seconds, for the other (database B)....well I haven't waited long enough yet, but over 10 minutes. I have dumped bot