Does anyone know of a way to force a nolock hint on all transactions issued by a certain user? I'd like to provide a login for a support team to query the production system, but I want to protect it by forcing a nolock on everything they do. I'm using SQL Server 2005. ...
Hi all, I have been working in a web project(asp.net) for around six months. The final product is about to go live. The project uses SQL Server as the database. We have done performance testing with some large volumes of data, results show that performance degrades when data becomes too large, say 2 million rows (timeout issues, delayed ...
In StackOverflow podcast no. 19, Joe describe Fogcreek's decision to have one database PER client instead of one database for ALL clients. That kinda sets me thinking about the following. Assuming I have 1000 users. Each user has 100 clients. Each client has 1000 products. So that means I'm gonna have 1000 x 100 x 1000 = 100,000,00...
Are hints really necessary for every sql statement? We have a dba who is anal about it and asks us to put hints on every select and update statements in our stored procs. Is this really necessary? ...
In SQLSERVER 2005, I'm using table-valued function as a convenient way to perform arbitrary aggregation on subset data from large table (passing date range or such parameters). I'm using theses inside larger queries as joined computations and I'm wondering if the query plan optimizer work well with them in every condition or if I'm bett...
From Joel Spolsky's article on leaky abstractions: [C]ertain SQL queries are thousands of times slower than other logically equivalent queries. A famous example of this is that some SQL servers are dramatically faster if you specify "where a=b and b=c and a=c" than if you only specify "where a=b and b=c" even though the result set i...
In output of explain command I found two terms 'Seq Scan' and 'Bitmap heap Scan'. Can somebody tell me what is the difference between these two types of scan? (I am using PostgreSql) ...
I originally had my field set as latin1_swedish_ci, which I changed to utf8_general_ci (both field and table) and then found my query went from ~1.8 seconds to ~3.3. I have an index on the field and have even recreated the index (delete then add). The field is used in an order by clause. Any ideas if there might be a problem or is this ...
I have the following query: SELECT DISTINCT c.id FROM clients AS c LEFT JOIN client_project AS cp ON (cp.client_id = c.id) WHERE cp.project_id = 1 AND c.active_flag = 1 ORDER BY c.client_name If I remove the order by, the query takes 0.005 seconds. With the order by, the query takes 1.8-1.9 seconds. I have an index on client_name....
Hi, I was wondering if it which is faster when trying to, for example, check how many posts are in a particular thread on a forum. Should I... (a) Go through each post in the database with the particular thread ID, and count how many rows or (b) Add one to a separate column in the threads database every time a thread is made, and then...
I have some queries that are causing timeouts in our live environment. (>30 seconds) If I run profiler and grab the exact SQL being run and run it from Management Studio then they take a long time to run the first time and then drop to a few hundred miliseconds each run after that. This is obviously SQL caching the data and getting it ...
Hi all, image this two tables: Table: Item Columns: ItemID, Title Table: Tag Columns: TagID, ItemID, Title Which is the best way (without changing table structure (yes, I don't mind if they are not normalized)) to retrieve a list of items with all their tags attached using less possible query (i.e. not doing 11 queries to retrieve 10...
I'm building a MySQL query to determine how many items from each of several categories appear in a given date range. My initial attempt looked like this: select Title, (select count(*) from entries where CategoryID=1 and Date >= @StartDate and Date <= @EndDate) as Cat1, (select count(*) from entries where CategoryID=2 and Da...
Assume I've a sql statement like the following with the variable @FOO set somewhere earlier in code: SELECT FIELDLIST FROM TABLE WHERE (FIELD = @FOO OR @FOO IS NULL) Is the query optimizer smart enough to do the second side of the OR first (@FOO IS NULL) because (another assumption) it is faster to do a null check than it is to d...
Consider the following 2 tables: Table A: id event_time Table B id start_time end_time Every record in table A is mapped to exactly 1 record in table B. This means table B has no overlapping periods. Many records from table A can be mapped to the same record in table B. I need a query that returns all A.id, B.id pairs. Something lik...
I have a pathological issue with SQL, so I usually sort all of my database issues by building quickie software applications to handle my SQL problems. (as I am also doing in this case) Thanks to StackOverflow I think I can be shamed into correctness, so I would like to learn how to make this kind of SQL troubleshooting in actual SQL o...
Given the following table: Table events id start_time end_time Is there a way to quickly search for a constant? E.g. SELECT * FROM events WHERE start_time<='2009-02-18 16:27:12' AND end_time>='2009-02-18 16:27:12' I am using MySQL. Having an index on either field still has to check a range. Moreover an index on both fields wi...
I have a table with index (autoincrement) and integer value. The table is millions of rows long. How can I search if a certain number appear in the last n rows of the table most efficiently? ...
We have a query to remove some rows from the table based on an id field (primary key). It is a pretty straightforward query: delete all from OUR_TABLE where ID in (123, 345, ...) The problem is no.of ids can be huge (Eg. 70k), so the query takes a long time. Is there any way to optimize this? (We are using sybase - if that matters). ...
I am working on optimizing a SQL query that goes against a very wide table in a legacy system. I am not able to narrow the table at this point for various reasons. My query is running slowly because it does an Index Seek on an Index I've created, and then uses a Bookmark Lookup to find the additional columns it needs that do not exist ...