I have a large table in Microsoft SQL Server 2008. It has two indexes. One index having column A descending order and another index having the column A ascending with some other columns. My …
Tag: performance
Slow sql statement when using variables
I have the following SQL statement running against a MariaDB 10.1.26 with ~2.000 rows with instant results. When I use the following statement with variables for the datetime fields, the execution takes ~5.5 seconds. The more data rows I have, the longer it takes to execute the statement. Seems like the variables change the behaviour of the statement somehow. What’s
Indexed View vs. Table
I had a long running query (relative to the data) that was hitting an indexed view. I thought an indexed view was physically stored data? I dumped everything into a table and ran the same query …
VIEW – INDEX SQL SERVEr
Does anyone know another way to run the query below more efficiently? I’m using SQL Server 2014. I’m facing a problem if i create index on table or on view . On table 2 (TB_FATURA_ITEM_TRANSACAO_HST) , has stored 50 million rows . I was wondering using Schemabiding on View , but I was looking in some articles that is not
Heap big table with performance issues
I have a business case where I need to store all details about invoices from vehicle workshop (invoice number, dealer,vehicle number, peace, nature of service , …). I have more than 50 columns which are all selected in the same query. In my treatment I need to do some specific treatment in order to get cleansed data and some basic
How does a recursive correlated expression speed up distinct queries?
I found this post about speeding up distinct queries: Super-fast DISTINCT using a recursive CTE: — 15ms CPU The recursive CTE is 100 times more efficient 🙂 This kind of speedup would be extremely valuable for my current project, but I am not sure in which cases this approach is beneficial. To be honest: I don’t get why this speeds
Query with rownum got slow
I have got a table table with 21 millions records of which 20 millions meet the criterion col1= ‘text’. I then started iteratively to set the value of col2 to a value unequal to NULL. After I have mutated 10 million records, the following query got slow, that was fast in the beginning: I noticed that as soon as I
which gives best performance?
Please go through the following query, and help me to understand which one is efficient and how? In logical query execution, where clause will be executed after completion of join, so I thought the …
Does Order of Fields of Multi-Column Index in MySQL Matter
I know the importance of indexes and how order of joins can change performance. I’ve done a bunch of reading related to multi-column indexes and haven’t found the answer to my question. I’m curious if I do a multi-column index, if the order that they are specified matters at all. My guess is that it would not, and that the
PostgreSQL – Query Optimization
I have this below query which takes about 15-20 secs to run. with cte0 as ( SELECT label, date, CASE WHEN Lead(label || date || “number”) OVER (PARTITION BY label || date || “number” ORDER BY “label”, “date”, “number”, “time”) IS NULL THEN ‘1’::numeric ELSE ‘0’::numeric END As “unique” FROM table_data LEFT JOIN table_mapper ON table_mapper.”type” = table_data.”type” WHERE Date