Tag Info

Hot answers tagged

8

My guess is, that because I have an aggregation, the server has to process all rows anyways, therefore the impact is not that high. Speaking from a SQL Server perspective, it depends. Here is an overview of what it depends on, and why: Row Goals Adding a top-level TOP (n) clause (with or without ORDER BY) has the same row goal effect as if a FAST (n) ...


5

You can add the query hint OPTION (RECOMPILE). This effectively tells SQL Server, "Give me a plan to execute this query one time, but don't save it in the plan cache". Take a peek at Kendra Little's article on RECOMPILE hints and execution plan caching. She covers all the uses and potential effects. RECOMPILE Hints and Execution Plan Caching Also see: ...


5

The addition of the redundant predicate can make a difference in SQL Server. In the execution plans below notice the @1 in the first plan vs the literal 'foo' in the second plan. This indicates that SQL Server considered the first query for simple parameterisation to promote execution plan reuse - however the comparison of two constants prevents this ...


4

Iterative approaches do have their place, but generally speaking it is much better to use set based approaches when working with databases. I've heard that the math says that "set based solutions will be faster than iterative solutions in the vast majority of the cases" several times, but I can't seem to find a good reference at the moment. If you really ...


4

Regardless of the length you define for your varchar column, the storage space used by an empty column will be the same. The CHAR and VARCHAR Types This only addresses the space used by the varchar column and does not consider the total storage space used by the row, its indexes, primary keys and other columns. As ypercube mentions in his comment, there ...


3

Housekeeping: I am using SQL Server Microsoft SQL Server 2012 - 11.0.5343.0 (X64) Solution: You can use WITH RECOMPILE in your stored procedure. Start by creating a test database, table, some indexes, and finally test data. USE [master]; GO IF DATABASEPROPERTYEX (N'test', N'Version') > 0 BEGIN ALTER DATABASE [test] SET SINGLE_USER WITH ...


2

Bug 11872813 - V$PGA_TARGET_ADVICE VIEW IS EMPTY. After setting PGA_AGGREGATE_TARGET > approximately 12G the V$PGA_TARGET_ADVICE view is empty. This issue will be fixed in version 12.1 and a fix is expected to be included in patch set 11.2.0.4. Apply Patch 11872813


2

From the Innodb Physical Row Structure, bulletpoint #7 under REDUNDANT ROW_FORMAT An SQL NULL value reserves one or two bytes in the record directory. Besides that, an SQL NULL value reserves zero bytes in the data part of the record if stored in a variable length column. In a fixed-length column, it reserves the fixed length of the column in the data ...


2

I was wondering whether you want to group by well_name for all different pull date or not. Right now, a well with X dates gets X rows. If you want the X date/rows to become 1, I will update the queries. Query 1 uses left join Query 2 uses pivot SQL Fiddle (Left Join): ;with data as( Select Pull_Date, Well_Name, Part, PartPN, Part_SN , id = ...


2

This should work. I took the original CTE, partitioned by part and date and numbered those. This gave every part on each date it's own number starting with 1. Then in the case statement, adding WHERE Number = N allows it so that each part is unique. Finally, the column names were modified to have their respective numbers. Since this is unwieldy, ...


2

Instead of using a sub query you could use a LEFT JOIN. That way you negate the need for a sub query which is being looked at for each image. LEFT JOIN seen ON seen.image_id = images.id AND seen.user_id = $user_id Then only get rows that haven't been joined. WHERE seen.id IS NULL


2

If Shanooooon's suggestion is not enough, then SELECT (stuff from x, plus picture stuff) FROM ( SELECT ... (everything except `picture` stuff) LIMIT 30 ) x LEFT JOIN pictures ON x.picture_id = pictures.id This helps because it will reach into pictures only 30 times, not 1000 times.


1

SELECT u.usrid, u.username, u.rank, u.extras, x.dtime FROM ( SELECT userid, MAX(datetime) dtime FROM log GROUP BY userid HAVING dtime < NOW() - INTERVAL 1 YEAR ) AS x JOIN users u ON u.usrid = x.userid WHERE ( u.rank = 'P' OR u.extras LIKE '%W%' ) ORDER BY ...


1

You could use dynamic SQL to loop through the years. DECLARE @SQL NVARCHAR(MAX) SET @SQL = 'SELECT '; DECLARE @intYear INT SET @intYear = 2013 WHILE (@intYear < Year(GETDATE())) BEGIN SET @SQL = @SQL + '(SELECT SUM(EmployeePaid)*2 AS CurrYear FROM TblRecords WHERE FYear = ' + CAST(@intYear as CHAR(4)) + ') as Total' + CAST(@intYear as CHAR(4)) + ...


1

If you are still inserting data for 2014, then you risk problems with this method, because rows inserted between steps 2 and 5 are going to end up getting dropped rather than moved. If you are not still inserting data for 2014, then I think you should change step 5 to "rewrite the trigger to throw an error upon insertion of 2014 data" and move it up to be ...


1

Paul White's answer is wonderful from the SqlServer point of view. I know this site isn't really about C# code review but I feel like I would be remiss if I didn't mention a couple of things about your data access code. Plus some errors in your C# code affect your stated assumptions. Firstly, your sample code may have been edited for brevity but just in ...


1

The problem as shown is transforming relational calculus, of which SQL is a variant, into relational algebra, which consists of the original operators Codd defined on relations. I will assume that the EMP, ASG, and PROJ represent employees, projects, and the assignment of employees to projects. The query, as stated in relational calculus, is asking for the ...



Only top voted, non community-wiki answers of a minimum length are eligible