The tag has no wiki summary.

learn more… | top users | synonyms

0
votes
1answer
38 views

Is there any way to get the estimate cost of executing a query in MySQL?

In PostgreSQL, EXPLAIN or EXPLAIN ANALYZE will show the estimate cost of executing a query. But EXPLAIN in MySQL doesn't provide this information. How can I get the estimate cost without installation ...
0
votes
1answer
37 views

Fetching User Recent Status

I am running mysql with MyISAM storage engine to store user status(user updates data). To load user recent updates data i am using this query: $limit = 20; $uid = 3; SELECT * FROM userstatus WHERE ...
3
votes
1answer
43 views

SHOWPLAN does not display a warning but “Include Execution Plan” does for the same query

I was running the following query in SSMS and it showed me a warning in the "Include Execution Plan" window select CLIENT_GUID, PMT_AMOUNT, DATE_COMPLETED from ( select payment.CLIENT_GUID, ...
2
votes
2answers
65 views

Estimated vs Actual Number of Rows

I have 5 tables that contain data split by quarter each table has a similar structure to the following: ID_Column bigint PK, EventType int, DateStamp datetime PK CustomerId int, ARG_1 nvarchar(32), ...
2
votes
0answers
59 views

Does Oracle ADO.NET varchar parameter size influence query plan reuse?

I am considering to apply a fix which will generate constant parameter sizes for (n)varchar parameters. The application is based on NHibernate with the old OracleClientDriver (using Microsoft Oracle ...
1
vote
1answer
265 views

How bad is query plan pollution (too many equivalent plans in cache)?

Question currently affects C#/.NET, DB access based on ADO.NET and SQL Server 2008 R2, but I think it applies to other databases as well. I noticed some old modules of a system have non-optimal SQL ...
1
vote
1answer
34 views

Estimating a Size of Joining a Relation with itself

I'm studying size estimation of logical query plans in order to select a physical query plan. I was wondering what is the size of joining (natural join) a relation to itself? e.g R(a,b) JOIN R(a,b)
1
vote
0answers
94 views

Postgres - Partitioning old tables, partition query planning, optimisation

I've been trying to optimise our database, but not being a DBA and not having done a whole lot of tuning before I'm struggling a little with "too many options". I've already run pg_tune and bumped a ...
1
vote
2answers
283 views

Improve performance with the WHERE NOT IN sub-select clause

In the following query, I have to count transactions for each customer. [EDIT]However, I have to exclude from the result set entirely, customers that have a transaction older than one year. ...
2
votes
2answers
329 views

How can you tell when a query plan reuse is causing performance problems

When investigating a performance issue, a workmate suggested running DBCC FREEPROCCACHE to clear the plan cache. He came to this conclusion after noticing performance improved after a reboot. This ...
8
votes
2answers
627 views

When does sp_executesql refresh the query plan?

You'll have to forgive my naivety as I'm not a DBA but my understanding is that over time the statistics of a database change and a stored procedure must be recompiled to keep the query plan up to ...
2
votes
2answers
600 views

Should I refresh query plan cache

Please let me explain my problem and situation: I have a web application - MVC3, MSSQL Server 2005, LinqToSQL. It has been running great until one fine morning I pushed a lot of rows to a table that ...
6
votes
1answer
634 views

How does SQL Server generate a query execution plan that adds up to 6,000%? [duplicate]

Today I was on The Heap and was looking at a query plan for something I thought could be improved. However, it created something that shook my belief in the SQL Server query optimizer. Can I still ...
3
votes
2answers
2k views

PostgreSQL Sequential Scan instead of Index Scan Why?

Hi All I've got a problem with my PostgreSQL database query and wondering if anyone can help. In some scenarios my query seems to ignore the index that I've created which is used for joining the two ...
2
votes
2answers
141 views

Query taking too much time

My query is taking too much time to execute. Please help me here. mysql> explain select pcm.catalog_id from cat_produ_catal_map_defer pcm, cat_catal_catal_map_defer ccm, cat_catal_defer c ...
6
votes
1answer
297 views

Estimated versus actual query plan with function calls

I have this query on SQL server, a merge replication query: SELECT DISTINCT b.tablenick, b.rowguid, c.generation, sys.fn_MSgeneration_downloadonly ( c.generation, ...
1
vote
2answers
151 views

postgresql sticking “enable_seqscan=False” in a UDF sensible?

I have a query which is taking 40 seconds to execute over the 80ms that it should if postgres where to filter off the primary key rather than doing a sequential scan. I have to force postgres to ...
3
votes
1answer
1k views

Retrieve XML of large plan from plan cache

I'm trying to view (graphically) a large query plan that is in the plan cache and am running into problems where the XML is truncated in the query results. This is the query I'm using: SELECT TOP 1 ...