Hello SQL Server engine experts; please share with us a bit of your insight... As I understand it, INCLUDE columns on a non-clustered index allow additional, non-key data to be stored with the index pages. I am well aware of the performance benefits a clustered index has over a non-clustered index simply due to the 1 fewer step the eng...
I have a table containing hierarchical data. There are currently ~8 levels in this hierarchy. I really like the way the data is structured, but performance is dismal when I need to know if a record at level 8 is a child of a record at level 1. I have PL/SQL stored functions which do these lookups for me, each having a select * from tb...
Hi al, I need to optimize the following query but 'm not able to wonder how. select distinct v.codvia, v.codproine, v.codmunine, tv.SIMBOLO as SIMBOLO_TIPO_VIA, tv.NOMBRE as TIPO_VIA, c.nombrevia as NOMBRE_VIA, v.cp, m.nombre as NOMBRE_MUNICIPIO , pr.nombre as NOMBRE_PROVINCIA from tdinumvias v, ...
I currently have a query that contains a self-join to query all the direct and indirect managers of an employee from a table that stores company organization info using the nested sets model. In this SQL notation, numbers preceded by a colon (e.g. :1) are variables: select parent.empid, parent.depth from RelationshipMgr as node join Re...
I have a very large (80+ million row) de-normalized MySQL table. A simplified schema looks like: +-----------+-------------+--------------+--------------+ | ID | PARAM1 | PARAM2 | PARAM3 | +-----------+-------------+--------------+--------------+ | 1 | .04 | .87 | .78 | +-------...
How to optimize the response time for the following query: SELECT /*+parallel */ cc.customer_id AS customer_id, cc.title1 AS title1, cc.forename1 AS forename1, cc.forename2 AS forename2, cc.surname1 AS surname1, cc.surname2 AS surname2, cc.company_flag AS company_flag,...
I need to query the orders table to get a count of all orders for yesterdays transactions, grouped by the ship date. Then I need to have an additional column to give the total orders for the ship date for all transactions. When I added this second column, time of processing grew exponentially (which was expected) to 109s. Is there any...
First off, here's my SQL query: SELECT research_cost, tech_name, (SELECT research_cost FROM technologies WHERE research_cost <= USERS_RESEARCH_POINTS_VALUE ORDER BY research_cost DESC LIMIT 1) as research_prev, (SELECT cost FROM technology_costs WHERE id = 18 LIMIT 1) as te...
Hello, I want to know how I can optimize my Doctrine queries because some of my queries are too long. I don't want a method particularly to my querie, but common methods I can apply to all requests, because I don't find information about this. ...
Here is my table structure MyTable ----------- ObjectID int (Identity), -- Primary Key FileName varchar(10), CreatedDate datetime ........... ........... ........... I need to get the time taken to create record in a file... ie... Time elapsed between the previous record in the same file and the current record of the same f...
Hey Guys, I am currently designing a dashboard feed that functions similar to the facebook feed. The feed will contain the following: Notify user of new users Show referrals sent to users, referrals received News from the administrator Up comming events. Each of the above is stored in it's own table but on the feeds page, they shoul...
I am creating sql queries and I have read in one book that when using NOT operator or LIKE operators Indexes do not work. How much true this statement is. How can we avoid this if the statement is true. How a query should be made to do the same work avoiding these operators. What all other areas are there in sql server where Indexes are...
I have two indexed views, v_First and v_Second. These views work great when I have a WHERE clause that filters only based on one of these views, however as soon as I have filter criteria based on both views I get two clustered index scans and poor performance as a result. My query is: SELECT * FROM dbo.v_First (NOEXPAND) JOIN dbo.v_Se...
I'm not sure if "linearization" is the proper term, but I need a query that will output something like this: item_name item_price first_name last_name ----------------------------------------------- 'camera' '100' 'Little' 'Timmy' 'computer' '200' 'Little' 'Timmy' Here's my DB: http://pastebin.com/iS4QKHEb...
I want to know what are various methods by which I can monitor which of my procedure, query is taking more time on various components(CPU cycle, scan time etc.) than already set threshold value. I want it to be logged as well. Whenever uses my site and calling some procedure. I want to make a log of all procedures crossing my threshold....
Currently I am doing one query, with 3 sub queries, all queries are on the same table, all queries have different where clauses I have thought about doing a group by however this would destroy the SUM() here is the query SELECT SUM(club) AS club, (SELECT COUNT(id) FROM action_6_members WHERE SUBSTR(CODE, 1, 1) = '9') AS 5pts, (SELE...
Hi All, I have a table I'm working on that has around 3 million tuples. It doesn't change too often (a few updates or inserts per week) and is read a lot. (Please don't comment on the varchar of length 1. I know, I know). Column | Type | Modifiers -------------+--------...
Using MySQL So i'm trying to improve upon a query I've written. My current query works, but i feel like i could be more efficient Essentially, I have a table that lists 'who talks to who and how much'. The records look like this: email name status count prod_ref (I'll post an example set with example output at the end of the...
I'm running the following query in MYSQL select distinct straight_join cu.entryid entryid, t0.tokpos starting_position, t3.tokpos ending_position, t0.idxsent idxsent, 'TOKENS_44_340' tablename from TOKENS_44_340 t0, constraints_appraisal cu, TOKENS_44_340 t1, TOKENS_44_340 t2, TOKENS_44_340 t3 where t0.toke...
I'm currently trying to optimize a "bottleneck" function that is called really ofen in an application. In the application in question, options can be selected. But some options can be restricted by other. For example, when option "A" is selected, it restricts the selection of option "B". These restriction links are saved to a table whi...