Tag Info

New answers tagged

0

You can use Geohashing (Geohasing) You have to create a geohash for every POI in your table. And then based on the precision that you choose you can use LEFT(your_geohash_column,'c1','c3','c4','c5','c6',c7','c8') Where the c's are the 8 neighbors from the center calculated by your desired precision. Keep in mind that you are limiting the results with the ...


0

1M/year is 2 per minute. You can do a lot of updating in 30 seconds. 1M/day (12/second) would be more exciting. Do you need to rerun the summary queries? Can't you just do a subtract via an UPDATE? Possibly fast enough to be synchronous. How often do you need to post a correction? If it is rare, consider adding a row to the summary table with a ...


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 ...


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


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)) + ...


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 ...


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 ...


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 = ...


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 ...


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, ...


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) ...


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 ...


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 ...


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 ...



Top 50 recent answers are included