Tag Info

New answers tagged

4

The performance difference in your query is well explained by MG. I am going to address this: I've always believed that * queries should be avoided specifically for performance reasons. select * carries no particular penalties by itself, it is problematic when misused. In a single-table query it works just fine. now join that table to another with 20 ...


1

You can use derive table to aggregate all your column values in a single column and do ordering and counting in the derived table. Example below. Select item0 as item, count(*) as cnt from (SELECT item0 from finalBuild UNION ALL SELECT item1 from finalBuild UNION ALL SELECT item2 from finalBuild) as item group by item0 order by count(*) desc;


24

The phrase ORDER BY 1 refers to different columns; in the first it will be id, in the second val. Since id is the key it will be indexed and the order by will be a trivial amount of work. To order by val, however, the system will have to retrieve every row, sort the complete table by val, then choose just one of those rows. Change both queries to order by ...


0

You have multiple values in the DATE_ADDED field for each distinct pair of ORDERS_ID/VALUE results you see returned when you execute the query with only two columns selected.


0

You can try this one for solve your problem, Select name From expenses Where name like 'vendor0%' and date like '2013%' and credit IS NULL Group by name Order by name asc


0

In PostgreSQL you can do it using column aliasing: postgres=# CREATE TABLE product (product_id INTEGER, name TEXT); CREATE TABLE postgres=# INSERT INTO product (product_id, name) VALUES (1, 'product 1'), (2, 'product 2'); INSERT 0 2 postgres=# SELECT p.id FROM product p (id); id ---- 1 2 (2 rows) Don't know if it works the same way in MySQL, ...


1

To answer your immediate question, one way round it is this: SELECT SomeColumns FROM TABLE WHERE ( @Values IS NULL ) OR COLUMN IN (SELECT Id FROM #Parameters) As mentioned, you could avoid the temporary table by passing the parameters as a TVP, but only if the caller can do that also. Or you could avoid it by in-lining the function call SELECT ...


0

TLDR To cut a long story short, if performance is a concern, you should test thoroughly because there are a few potential surprises. If you're lucky, the latest version of SQL Server may have resolved the issues I experienced. Aaron's answer is similar to what I used to do with with optional filter parameters. I.e. WHERE Col1 = ISNULL(@Param, Col1) ...


3

You can do it this way (notice this avoids the unnecessary #temp table): SELECT SomeColumns FROM dbo.TABLE AS t LEFT OUTER JOIN dbo.FunctionName(',',@Values) AS p ON t.COLUMN = p.Value WHERE t.COLUMN = CASE WHEN @Values IS NULL THEN t.COLUMN ELSE p.Value END; But much, much, much better to pass @Values as a TVP instead of a string you have to split. No ...


0

You can do it by using this function : GO -- ============================================= -- Description: Splits comma separated numbers in a string to a table rows -- ============================================= CREATE FUNCTION [usp_CsStringToTable] ( @IDs nvarchar(max) ) RETURNS @SplitValues TABLE ( ID NVARCHAR(max) ) AS BEGIN -- Fill the ...


0

For your purpose @Daniel's simple query should do the trick: select repeat('A', (3 * 2^20)::int); -- "3 MB" "Size" depends. There is the size on disk, with or without overhead, with or without padding, with or without compression, with or without indexes / toast table / free space map ... There is also the size of RAM needed to store the same data ...


0

There is no actual serial data type. serial is just a notational convenience. The resulting data type is integer for serial or bigint for bigserial Related answer on SO with more details: Auto increment SQL function You'd have to be more specific for a more specific answer.


1

There you go: SELECT Client, SUM(IF(Status = "A", Size, 0)) AS A, SUM(IF(Status = "B", Size, 0)) AS B FROM yourtable GROUP BY Client


1

This works, there may be something more elegant: insert select 1 as client, 'A' as [status], 500 as 'Size' into #temp insert into #temp select 1 as client, 'B' as [status], 1500 as 'Size' insert into #temp select 2 as client, 'A' as [status], 2000 as 'Size' insert into #temp select 2 as client, 'B' as [status], 800 as 'Size' select t.client, ...


1

The TOP does nothing - based on the fact that it's "100%". The first query is essentially the same as: WHERE (ds.DocumentSourceName NOT LIKE 'ZZ%'), but the second has more filters...


1

Lots of answers why select * is wrong, so I'll cover when I feel it's right or at least OK. 1) In an EXISTS, the content of the SELECT part of the query is ignored, so you can even write SELECT 1/0 and it won't error. EXISTS just verifies that some data would return and returns a boolean based on that. IF EXISTS( SELECT * FROM Table WHERE X=@Y ) 2) ...


2

There's one more small reason to not use SELECT *: if the order of the columns returned changes, your application will break... if you're lucky. If you're not, you'll have a subtle bug that could go undetected for a long time. The order of fields in a table is an implementation detail which should never be considered by applications, as the only time it is ...


2

It can be a problem if you put the 'Select * ...' code in a program, because, as pointed out earlier, the database might change over time and have more columns than what you expected when you wrote the query. This can lead to program failure (best case) or the program might go on its merry way and corrupt some data because it's looking at field values that ...


2

It is physically and problematically allowed to use select * from table, however, it's a bad idea. Why? First of all, you'll find that you're returning columns that you don't need (resource heavy). Secondly, it'll take longer on a large table than naming the columns because when you select *, you're actually selecting the column names from the database ...


8

Performance: A query with SELECT * will probably never be a covering query (Simple talk explanation, Stack Overflow explanation). Future-proofing: Your query might return all seven columns today but if someone adds five columns over the next year then in a year your query is returning twelve columns, wasting IO and CPU. Indexing: If you want your views and ...


7

In SQL Server 2012, (or any version from 2005 up), using SELECT *... is only a possible performance problem in the top-level SELECT statement of a query. So it is NOT a problem in Views(*), in subqueries, in EXIST clauses, in CTEs, nor in SELECT COUNT(*).. etc., etc. Note, that this is probably also true for Oracle, and DB2, and maybe PostGres (not sure), ...


16

In addition to the answer already provider, I feel that it's worth pointing out that developers are often too lazy when working with modern ORM's such as Entity Framework. Whilst DBA's try their hardest to avoid SELECT *, developers often write the semantically equivalent eg, in c# Linq: var someVariable = db.MyTable.Where(entity => entity.FirstName == ...


37

If you SELECT COUNT(*) FROM TABLE that only returns one row (the count), is relatively light, and is the way to get that datum. And SELECT * is not a physical no-no, in that it is legal and allowed. However, the problem with SELECT * is that you can cause a lot more data movement. You operate on every column in the table. If your SELECT only includes a ...



Top 50 recent answers are included