Search Results

Search found 17240 results on 690 pages for 'query'.

Page 4/690 | < Previous Page | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >

• Converting sql query to EF query - nested query in from

  - by vdh_ant

  Hey guys Just wondering how the following sql query would look in linq for Entity Framework... SELECT KPI.* FROM KeyPerformanceIndicator KPI INNER JOIN ( SELECT SPP.SportProgramPlanId FROM SportProgramPlan PSPP INNER JOIN SportProgramPlan ASPP ON (PSPP.SportProgramPlanId = @SportProgramPlanId AND PSPP.StartDate >= ASPP.StartDate AND PSPP.EndDate <= ASPP.EndDate ) AS SPP ON KPI.SportProgramPlanId = SPP.SportProgramPlanId Cheers Anthony

  Read the article

• SQL SERVER – Denali Feature – Zoom Query Editor

  - by pinaldave

  SQL Server next version ‘Denali’ is coming up with very neat feature which can be used while presentations, group discussion or for people who prefers large fonts. I have increased the font size to 400 percentage and for the same reason they are very large. You can adjust the font size which is convenient to you. One more reason to go for next version of SQL Server. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology

  Read the article

• Adding an LOV to a query parameter (executeWithParams)

  - by shay.shmeltzer

  I showed in the past how you can use the executeWithParams operation to build your own query page to filter a view object to show specific rows. I also showed how you can make the parameter fields display as drop down lists of values (selectOneChoice). However this week someone asked me if you can have those parameter fields use the advanced LOV component. Well if you just try and drag the parameter over, you'll see that the LOV option is not there as a drop option. But with a little bit of hacking around you can achieve this. (without actual Java coding). Here is a quick demo:

  Read the article

• How can I join this 2 queries?(A select query with join and An unpivot query)

  - by MANG KANOR

  Here are my two queries SELECT EWND.Position, NKey = CASE WHEN ISNULL(Translation.Name, '') = '' THEN EWND.Name ELSE Translation.Name END, Unit = EW_N_DEF.Units FROM EWND INNER JOIN EW_N_DEF ON EW_N_DEF.Nutr_No = EWND.Nutr_No LEFT JOIN Translation ON Translation.CodeMain = EWND.Nutr_no WHERE Translation.CodeTrans = 1 ORDER BY EWND.Position And this is the unpivot one SELECT * FROM (SELECT N1,N2,N3,N4,N5,N6,N7,N8,N9,N10,N11,N12,N13,N14,N15,N16,N17,N18,N19,N20,N21,N22,N23,N24,N25,N26,N27,N28,N29,N30,N31,N32,N33,N34 FROM EWNVal WHERE Code=6035) Test UNPIVOT (Value FOR NUTCODE IN (N1,N2,N3,N4,N5,N6,N7,N8,N9,N10,N11,N12,N13,N14,N15,N16,N17,N18,N19,N20,N21,N22,N23,N24,N25,N26,N27,N28,N29,N30,N31,N32,N33,N34) )AS test Both Queries put out same number of rows but not columns, Is it possible to join this two? I tried the union but it has problems that I cant solve Thanks in advance!

  Read the article

• T-SQL Tuesday #13: Clarifying Requirements

  - by Alexander Kuznetsov

  When we transform initial ideas into clear requirements for databases, we typically have to make the following choices: Frequent maintenance vs doing it once. As we are clarifying the requirements, we need to determine whether we want to concinue spending considerable time maintaining the system, or if we want to finish it up and move on to other tasks. Race car maintenance vs installing electric wiring is my favorite analogy for this kind of choice. In some cases we need to sqeeze every last bit...(read more)

  Read the article

• Function Folding in #PowerQuery

  - by Darren Gosbell

  Originally posted on: http://geekswithblogs.net/darrengosbell/archive/2014/05/16/function-folding-in-powerquery.aspxLooking at a typical Power Query query you will noticed that it's made up of a number of small steps. As an example take a look at the query I did in my previous post about joining a fact table to a slowly changing dimension. It was roughly built up of the following steps: Get all records from the fact table Get all records from the dimension table do an outer join between these two tables on the business key (resulting in an increase in the row count as there are multiple records in the dimension table for each business key) Filter out the excess rows introduced in step 3 remove extra columns that are not required in the final result set. If Power Query was to execute a query like this literally, following the same steps in the same order it would not be overly efficient. Particularly if your two source tables were quite large. However Power Query has a feature called function folding where it can take a number of these small steps and push them down to the data source. The degree of function folding that can be performed depends on the data source, As you might expect, relational data sources like SQL Server, Oracle and Teradata support folding, but so do some of the other sources like OData, Exchange and Active Directory. To explore how this works I took the data from my previous post and loaded it into a SQL database. Then I converted my Power Query expression to source it's data from that database. Below is the resulting Power Query which I edited by hand so that the whole thing can be shown in a single expression: let     SqlSource = Sql.Database("localhost", "PowerQueryTest"),     BU = SqlSource{[Schema="dbo",Item="BU"]}[Data],     Fact = SqlSource{[Schema="dbo",Item="fact"]}[Data],     Source = Table.NestedJoin(Fact,{"BU_Code"},BU,{"BU_Code"},"NewColumn"),     LeftJoin = Table.ExpandTableColumn(Source, "NewColumn"                                   , {"BU_Key", "StartDate", "EndDate"}                                   , {"BU_Key", "StartDate", "EndDate"}),     BetweenFilter = Table.SelectRows(LeftJoin, each (([Date] >= [StartDate]) and ([Date] <= [EndDate])) ),     RemovedColumns = Table.RemoveColumns(BetweenFilter,{"StartDate", "EndDate"}) in     RemovedColumns If the above query was run step by step in a literal fashion you would expect it to run two queries against the SQL database doing "SELECT * …" from both tables. However a profiler trace shows just the following single SQL query: select [_].[BU_Code],     [_].[Date],     [_].[Amount],     [_].[BU_Key] from (     select [$Outer].[BU_Code],         [$Outer].[Date],         [$Outer].[Amount],         [$Inner].[BU_Key],         [$Inner].[StartDate],         [$Inner].[EndDate]     from [dbo].[fact] as [$Outer]     left outer join     (         select [_].[BU_Key] as [BU_Key],             [_].[BU_Code] as [BU_Code2],             [_].[BU_Name] as [BU_Name],             [_].[StartDate] as [StartDate],             [_].[EndDate] as [EndDate]         from [dbo].[BU] as [_]     ) as [$Inner] on ([$Outer].[BU_Code] = [$Inner].[BU_Code2] or [$Outer].[BU_Code] is null and [$Inner].[BU_Code2] is null) ) as [_] where [_].[Date] >= [_].[StartDate] and [_].[Date] <= [_].[EndDate] The resulting query is a little strange, you can probably tell that it was generated programmatically. But if you look closely you'll notice that every single part of the Power Query formula has been pushed down to SQL Server. Power Query itself ends up just constructing the query and passing the results back to Excel, it does not do any of the data transformation steps itself. So now you can feel a bit more comfortable showing Power Query to your less technical Colleagues knowing that the tool will do it's best fold all the  small steps in Power Query down the most efficient query that it can against the source systems.

  Read the article

• SQL SERVER – Force Index Scan on Table – Use No Index to Retrieve the Data – Query Hint

  - by pinaldave

  Recently I received the following two questions from readers and both the questions have very similar answers. Question 1: I have a unique requirement where I do not want to use any index of the table; how can I achieve this? Question 2: Currently my table uses clustered index and does seek operation; how can I convert [...]

  Read the article

• I have written an SQL query but I want to optimize it [closed]

  - by ankit gupta

  is there any way to do this using minimum no of joins and select? 2 tables are involved in this operation transaction_pci_details and transaction SELECT t6.transaction_pci_details_id, t6.terminal_id, t6.transaction_no, t6.transaction_id, t6.transaction_type, t6.reversal_flag, t6.transmission_date_time, t6.retrivel_ref_no, t6.card_no,t6.card_type, t6.expires_on, t6.transaction_amount, t6.currency_code, t6.response_code, t6.action_code, t6.message_reason_code, t6.merchant_id, t6.auth_code, t6.actual_trans_amnt, t6.bal_card_amnt, t5.sales_person_id FROM TRANSACTION AS t5 INNER JOIN ( SELECT t4.transaction_pci_details_id, t4.terminal_id, t4.transaction_no, t4.transaction_id, t4.transaction_type, t4.reversal_flag, t4.transmission_date_time, t4.retrivel_ref_no, t4.card_no, t4.card_type, t4.expires_on, t4.transaction_amount, t4.currency_code, t4.response_code, t4.action_code, t3.message_reason_code, t4.merchant_id, t4.auth_code, t4.actual_trans_amnt, t4.bal_card_amnt FROM ( SELECT* FROM transaction_pci_details WHERE message_reason_code LIKE '%OUT%'|| message_reason_code LIKE '%FAILED%' /*we can add date here*/ UNION ALL SELECT t2.transaction_pci_details_id, t2.terminal_id, t2.transaction_no, t2.transaction_id, t2.transaction_type, t2.reversal_flag, t2.transmission_date_time, t2.retrivel_ref_no, t2.card_no, t2.card_type, t2.expires_on, t2.transaction_amount, t2.currency_code, t2.response_code, t2.action_code, t2.message_reason_code, t2.merchant_id, t2.auth_code, t2.actual_trans_amnt, t2.bal_card_amnt FROM ( SELECT transaction_id FROM TRANSACTION WHERE transaction_type_id = 8 ) AS t1 INNER JOIN ( SELECT * FROM transaction_pci_details WHERE message_reason_code LIKE '%appro%' /*we can add date here*/ ) AS t2 ON t1.transaction_id = t2.transaction_id ) AS t3 INNER JOIN ( SELECT* FROM transaction_pci_details WHERE action_code LIKE '%REQ%' /*we can add date here*/ ) AS t4 ON t3.transaction_pci_details_id - t4.transaction_pci_details_id = 1 ) AS t6 ON t5.transaction_id = t6.transaction_id

  Read the article

• Error 404 after rewrite query strings with htaccess

  - by Cristian

  I'm trying to redirect the URLs of a client's website like this: www.localsite.com/immobile.php?id_immobile=24 In something like this: www.localsite.com/immobile/24.php I'm using this rule in .htaccess but it returns a 404 error page. RewriteEngine On RewriteCond %{QUERY_STRING} ^id_immobile=([0-9]*)$ RewriteRule ^immobile\.php$ http://localsite.com/immobile/%1.php? [L] I have tried many other rules, but none work. What can I do?

  Read the article

• Sub query pass through

  - by SQL and the like

  Occasionally in forums and on client sites I see conditional subqueries in statements. This is where the developer has decided that it is only necessary to process some data under a certain condition.  By way of example, something like this : Create Procedure GetOrder @SalesOrderId integer, @CountDetails tinyint as Select SOH.salesorderid , case when @CountDetails = 1 then (Select count(*) from Sales.SalesOrderDetail SOD where SOH.SalesOrderID = SOD.SalesOrderID) end from sales.SalesOrderHeader...(read more)

  Read the article

• Does the order of columns in a query matter?

  - by James Simpson

  When selecting columns from a MySQL table, is performance affected by the order that you select the columns as compared to their order in the table (not considering indexes that may cover the columns)? For example, you have a table with rows uid, name, bday, and you have the following query. SELECT uid, name, bday FROM table Does MySQL see the following query any differently and thus cause any sort of performance hit? SELECT uid, bday, name FROM table

  Read the article

• Problem with JMX query of Coherence node MBeans visible in JConsole

  - by Quinn Taylor

  I'm using JMX to build a custom tool for monitoring remote Coherence clusters at work. I'm able to connect just fine and query MBeans directly, and I've acquired nearly all the information I need. However, I've run into a snag when trying to query MBeans for specific caches within a cluster, which is where I can find stats about total number of gets/puts, average time for each, etc. The MBeans I'm trying to access programatically are visible when I connect to the remote process using JConsole, and have names like this: Coherence:type=Cache,service=SequenceQueue,name=SEQ%GENERATOR,nodeId=1,tier=back It would make it more flexible if I can dynamically grab all type=Cache MBeans for a particular node ID without specifying all the caches. I'm trying to query them like this: QueryExp specifiedNodeId = Query.eq(Query.attr("nodeId"), Query.value(nodeId)); QueryExp typeIsCache = Query.eq(Query.attr("type"), Query.value("Cache")); QueryExp cacheNodes = Query.and(specifiedNodeId, typeIsCache); ObjectName coherence = new ObjectName("Coherence:*"); Set<ObjectName> cacheMBeans = mBeanServer.queryMBeans(coherence, cacheNodes); However, regardless of whether I use queryMBeans() or queryNames(), the query returns a Set containing... ...0 objects if I pass the arguments shown above ...0 objects if I pass null for the first argument ...all MBeans in the Coherence:* domain (112) if I pass null for the second argument ...every single MBean (128) if I pass null for both arguments The first two results are the unexpected ones, and suggest a problem in the QueryExp I'm passing, but I can't figure out what the problem is. I even tried just passing typeIsCache or specifiedNodeId for the second parameter (with either coherence or null as the first parameter) and I always get 0 results. I'm pretty green with JMX — any insight on what the problem is? (FYI, the monitoring tool will be run on Java 5, so things like JMX 2.0 won't help me at this point.)

  Read the article

• MySQL select query result set changes based on column order

  - by user197191

  I have a drupal 7 site using the Views module to back-end site content search results. The same query with the same dataset returns different results from MySQL 5.5.28 to MySQL 5.6.14. The results from 5.5.28 are the correct, expected results. The results from 5.6.14 are not. If, however, I simply move a column in the select statement, the query returns the correct results. Here is the code-generated query in question (modified for readability). I apologize for the length; I couldn't find a way to reproduce it without the whole query: SELECT DISTINCT node_node_revision.nid AS node_node_revision_nid, node_revision.title AS node_revision_title, node_field_revision_field_position_institution_ref.nid AS node_field_revision_field_position_institution_ref_nid, node_revision.vid AS vid, node_revision.nid AS node_revision_nid, node_node_revision.title AS node_node_revision_title, SUM(search_index.score * search_total.count) AS score, 'node' AS field_data_field_system_inst_name_node_entity_type, 'node' AS field_revision_field_position_college_division_node_entity_t, 'node' AS field_revision_field_position_department_node_entity_type, 'node' AS field_revision_field_search_lvl_degree_lvls_node_entity_type, 'node' AS field_revision_field_position_app_deadline_node_entity_type, 'node' AS field_revision_field_position_start_date_node_entity_type, 'node' AS field_revision_body_node_entity_type FROM node_revision node_revision LEFT JOIN node node_node_revision ON node_revision.nid = node_node_revision.nid LEFT JOIN field_revision_field_position_institution_ref field_revision_field_position_institution_ref ON node_revision.vid = field_revision_field_position_institution_ref.revision_id AND (field_revision_field_position_institution_ref.entity_type = 'node' AND field_revision_field_position_institution_ref.deleted = '0') LEFT JOIN node node_field_revision_field_position_institution_ref ON field_revision_field_position_institution_ref.field_position_institution_ref_target_id = node_field_revision_field_position_institution_ref.nid LEFT JOIN field_revision_field_position_cip_code field_revision_field_position_cip_code ON node_revision.vid = field_revision_field_position_cip_code.revision_id AND (field_revision_field_position_cip_code.entity_type = 'node' AND field_revision_field_position_cip_code.deleted = '0') LEFT JOIN node node_field_revision_field_position_cip_code ON field_revision_field_position_cip_code.field_position_cip_code_target_id = node_field_revision_field_position_cip_code.nid LEFT JOIN node node_node_revision_1 ON node_revision.nid = node_node_revision_1.nid LEFT JOIN field_revision_field_position_vacancy_status field_revision_field_position_vacancy_status ON node_revision.vid = field_revision_field_position_vacancy_status.revision_id AND (field_revision_field_position_vacancy_status.entity_type = 'node' AND field_revision_field_position_vacancy_status.deleted = '0') LEFT JOIN search_index search_index ON node_revision.nid = search_index.sid LEFT JOIN search_total search_total ON search_index.word = search_total.word WHERE ( ( (node_node_revision.status = '1') AND (node_node_revision.type IN ('position')) AND (field_revision_field_position_vacancy_status.field_position_vacancy_status_target_id IN ('38')) AND( (search_index.type = 'node') AND( (search_index.word = 'accountant') ) ) AND ( (node_revision.vid=node_node_revision.vid AND node_node_revision.status=1) ) ) ) GROUP BY search_index.sid, vid, score, field_data_field_system_inst_name_node_entity_type, field_revision_field_position_college_division_node_entity_t, field_revision_field_position_department_node_entity_type, field_revision_field_search_lvl_degree_lvls_node_entity_type, field_revision_field_position_app_deadline_node_entity_type, field_revision_field_position_start_date_node_entity_type, field_revision_body_node_entity_type HAVING ( ( (COUNT(*) >= '1') ) ) ORDER BY node_node_revision_title ASC LIMIT 20 OFFSET 0; Again, this query returns different sets of results from MySQL 5.5.28 (correct) to 5.6.14 (incorrect). If I move the column named "score" (the SUM() column) to the end of the column list, the query returns the correct set of results in both versions of MySQL. My question is: Is this expected behavior (and why), or is this a bug? I'm on the verge of reverting my entire environment back to 5.5 because of this.

  Read the article

• In MySQL, what is the most effective query design for joining large tables with many to many relatio

  - by lighthouse65

  In our application, we collect data on automotive engine performance -- basically source data on engine performance based on the engine type, the vehicle running it and the engine design. Currently, the basis for new row inserts is an engine on-off period; we monitor performance variables based on a change in engine state from active to inactive and vice versa. The related engineState table looks like this: +---------+-----------+---------------+---------------------+---------------------+-----------------+ | vehicle | engine | engine_state | state_start_time | state_end_time | engine_variable | +---------+-----------+---------------+---------------------+---------------------+-----------------+ | 080025 | E01 | active | 2008-01-24 16:19:15 | 2008-01-24 16:24:45 | 720 | | 080028 | E02 | inactive | 2008-01-24 16:19:25 | 2008-01-24 16:22:17 | 304 | +---------+-----------+---------------+---------------------+---------------------+-----------------+ For a specific analysis, we would like to analyze table content based on a row granularity of minutes, rather than the current basis of active / inactive engine state. For this, we are thinking of creating a simple productionMinute table with a row for each minute in the period we are analyzing and joining the productionMinute and engineEvent tables on the date-time columns in each table. So if our period of analysis is from 2009-12-01 to 2010-02-28, we would create a new table with 129,600 rows, one for each minute of each day for that three-month period. The first few rows of the productionMinute table: +---------------------+ | production_minute | +---------------------+ | 2009-12-01 00:00 | | 2009-12-01 00:01 | | 2009-12-01 00:02 | | 2009-12-01 00:03 | +---------------------+ The join between the tables would be engineState AS es LEFT JOIN productionMinute AS pm ON es.state_start_time <= pm.production_minute AND pm.production_minute <= es.event_end_time. This join, however, brings up multiple environmental issues: The engineState table has 5 million rows and the productionMinute table has 130,000 rows When an engineState row spans more than one minute (i.e. the difference between es.state_start_time and es.state_end_time is greater than one minute), as is the case in the example above, there are multiple productionMinute table rows that join to a single engineState table row When there is more than one engine in operation during any given minute, also as per the example above, multiple engineState table rows join to a single productionMinute row In testing our logic and using only a small table extract (one day rather than 3 months, for the productionMinute table) the query takes over an hour to generate. In researching this item in order to improve performance so that it would be feasible to query three months of data, our thoughts were to create a temporary table from the engineEvent one, eliminating any table data that is not critical for the analysis, and joining the temporary table to the productionMinute table. We are also planning on experimenting with different joins -- specifically an inner join -- to see if that would improve performance. What is the best query design for joining tables with the many:many relationship between the join predicates as outlined above? What is the best join type (left / right, inner)?

  Read the article

• SQL SERVER – Signal Wait Time Introduction with Simple Example – Wait Type – Day 2 of 28

  - by pinaldave

  In this post, let’s delve a bit more in depth regarding wait stats. The very first question: when do the wait stats occur? Here is the simple answer. When SQL Server is executing any task, and if for any reason it has to wait for resources to execute the task, this wait is recorded by SQL Server with the reason for the delay. Later on we can analyze these wait stats to understand the reason the task was delayed and maybe we can eliminate the wait for SQL Server. It is not always possible to remove the wait type 100%, but there are few suggestions that can help. Before we continue learning about wait types and wait stats, we need to understand three important milestones of the query life-cycle. Running - a query which is being executed on a CPU is called a running query. This query is responsible for CPU time. Runnable – a query which is ready to execute and waiting for its turn to run is called a runnable query. This query is responsible for Signal Wait time. (In other words, the query is ready to run but CPU is servicing another query). Suspended – a query which is waiting due to any reason (to know the reason, we are learning wait stats) to be converted to runnable is suspended query. This query is responsible for wait time. (In other words, this is the time we are trying to reduce). In simple words, query execution time is a summation of the query Executing CPU Time (Running) + Query Wait Time (Suspended) + Query Signal Wait Time (Runnable). Again, it may be possible a query goes to all these stats multiple times. Let us try to understand the whole thing with a simple analogy of a taxi and a passenger. Two friends, Tom and Danny, go to the mall together. When they leave the mall, they decide to take a taxi. Tom and Danny both stand in the line waiting for their turn to get into the taxi. This is the Signal Wait Time as they are ready to get into the taxi but the taxis are currently serving other customer and they have to wait for their turn. In other word they are in a runnable state. Now when it is their turn to get into the taxi, the taxi driver informs them he does not take credit cards and only cash is accepted. Neither Tom nor Danny have enough cash, they both cannot get into the vehicle. Tom waits outside in the queue and Danny goes to ATM to fetch the cash. During this time the taxi cannot wait, they have to let other passengers get into the taxi. As Tom and Danny both are outside in the queue, this is the Query Wait Time and they are in the suspended state. They cannot do anything till they get the cash. Once Danny gets the cash, they are both standing in the line again, creating one more Signal Wait Time. This time when their turn comes they can pay the taxi driver in cash and reach their destination. The time taken for the taxi to get from the mall to the destination is running time (CPU time) and the taxi is running. I hope this analogy is bit clear with the wait stats. You can check the Signalwait stats using following query of Glenn Berry. -- Signal Waits for instance SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits], CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits] FROM sys.dm_os_wait_stats OPTION (RECOMPILE); Higher the Signal wait stats are not good for the system. Very high value indicates CPU pressure. In my experience, when systems are running smooth and without any glitch the Signal wait stat is lower than 20%. Again, this number can be debated (and it is from my experience and is not documented anywhere). In other words, lower is better and higher is not good for the system. In future articles we will discuss in detail the various wait types and wait stats and their resolution. Read all the post in the Wait Types and Queue series. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL DMV, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Wait Stats, SQL Wait Types, T SQL, Technology

  Read the article

• SQL SERVER – Single Wait Time Introduction with Simple Example – Wait Type – Day 2 of 28

  - by pinaldave

  In this post, let’s delve a bit more in depth regarding wait stats. The very first question: when do the wait stats occur? Here is the simple answer. When SQL Server is executing any task, and if for any reason it has to wait for resources to execute the task, this wait is recorded by SQL Server with the reason for the delay. Later on we can analyze these wait stats to understand the reason the task was delayed and maybe we can eliminate the wait for SQL Server. It is not always possible to remove the wait type 100%, but there are few suggestions that can help. Before we continue learning about wait types and wait stats, we need to understand three important milestones of the query life-cycle. Running - a query which is being executed on a CPU is called a running query. This query is responsible for CPU time. Runnable – a query which is ready to execute and waiting for its turn to run is called a runnable query. This query is responsible for Single Wait time. (In other words, the query is ready to run but CPU is servicing another query). Suspended – a query which is waiting due to any reason (to know the reason, we are learning wait stats) to be converted to runnable is suspended query. This query is responsible for wait time. (In other words, this is the time we are trying to reduce). In simple words, query execution time is a summation of the query Executing CPU Time (Running) + Query Wait Time (Suspended) + Query Single Wait Time (Runnable). Again, it may be possible a query goes to all these stats multiple times. Let us try to understand the whole thing with a simple analogy of a taxi and a passenger. Two friends, Tom and Danny, go to the mall together. When they leave the mall, they decide to take a taxi. Tom and Danny both stand in the line waiting for their turn to get into the taxi. This is the Signal Wait Time as they are ready to get into the taxi but the taxis are currently serving other customer and they have to wait for their turn. In other word they are in a runnable state. Now when it is their turn to get into the taxi, the taxi driver informs them he does not take credit cards and only cash is accepted. Neither Tom nor Danny have enough cash, they both cannot get into the vehicle. Tom waits outside in the queue and Danny goes to ATM to fetch the cash. During this time the taxi cannot wait, they have to let other passengers get into the taxi. As Tom and Danny both are outside in the queue, this is the Query Wait Time and they are in the suspended state. They cannot do anything till they get the cash. Once Danny gets the cash, they are both standing in the line again, creating one more Single Wait Time. This time when their turn comes they can pay the taxi driver in cash and reach their destination. The time taken for the taxi to get from the mall to the destination is running time (CPU time) and the taxi is running. I hope this analogy is bit clear with the wait stats. You can check the single wait stats using following query of Glenn Berry. -- Signal Waits for instance SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits], CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits] FROM sys.dm_os_wait_stats OPTION (RECOMPILE); Higher the single wait stats are not good for the system. Very high value indicates CPU pressure. In my experience, when systems are running smooth and without any glitch the single wait stat is lower than 20%. Again, this number can be debated (and it is from my experience and is not documented anywhere). In other words, lower is better and higher is not good for the system. In future articles we will discuss in detail the various wait types and wait stats and their resolution. Read all the post in the Wait Types and Queue series. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL DMV, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Wait Stats, SQL Wait Types, T SQL, Technology

  Read the article

• Query returns too few rows

  - by Tareq

  setup: mysql> create table product_stock( product_id integer, qty integer, branch_id integer); Query OK, 0 rows affected (0.17 sec) mysql> create table product( product_id integer, product_name varchar(255)); Query OK, 0 rows affected (0.11 sec) mysql> insert into product(product_id, product_name) values(1, 'Apsana White DX Pencil'); Query OK, 1 row affected (0.05 sec) mysql> insert into product(product_id, product_name) values(2, 'Diamond Glass Marking Pencil'); Query OK, 1 row affected (0.03 sec) mysql> insert into product(product_id, product_name) values(3, 'Apsana Black Pencil'); Query OK, 1 row affected (0.03 sec) mysql> insert into product_stock(product_id, qty, branch_id) values(1, 100, 1); Query OK, 1 row affected (0.03 sec) mysql> insert into product_stock(product_id, qty, branch_id) values(1, 50, 2); Query OK, 1 row affected (0.03 sec) mysql> insert into product_stock(product_id, qty, branch_id) values(2, 80, 1); Query OK, 1 row affected (0.03 sec) my query: mysql> SELECT IFNULL(SUM(s.qty),0) AS stock, product_name FROM product_stock s RIGHT JOIN product p ON s.product_id=p.product_id WHERE branch_id=1 GROUP BY product_name ORDER BY product_name; returns: +-------+-------------------------------+ | stock | product_name | +-------+-------------------------------+ | 100 | Apsana White DX Pencil | | 80 | Diamond Glass Marking Pencil | +-------+-------------------------------+ 1 row in set (0.00 sec) But I want to have the following result: +-------+------------------------------+ | stock | product_name | +-------+------------------------------+ | 0 | Apsana Black Pencil | | 100 | Apsana White DX Pencil | | 80 | Diamond Glass Marking Pencil | +-------+------------------------------+ To get this result what mysql query should I run?

  Read the article

• How do I filter one of the columns in a SQL Server SQL Query

  - by Kent S. Clarkson

  I have a table (that relates to a number of other tables) where I would like to filter ONE of the columns (RequesterID) - that column will be a combobox where only people that are not sales people should be selectable. Here is the "unfiltered" query, lets call it QUERY 1: SELECT RequestsID, RequesterID, ProductsID FROM dbo.Requests If using a separate query, lets call it QUERY 2, to filter RequesterID (which is a People related column, connected to People.PeopleID), it would look like this: SELECT People.PeopleID FROM People INNER JOIN Roles ON People.RolesID = Roles.RolesID INNER JOIN Requests ON People.PeopleID = Requests.RequesterID WHERE (Roles.Role <> N'SalesGuy') ORDER BY Requests.RequestsID Now, is there a way of "merging" the QUERY 2 into QUERY 1? (dbo.Requests in QUERY 1 has RequesterID populated as a Foreign Key from dbo.People, so no problem there... The connections are all right, just not know how to write the SQL query!)

  Read the article

• MySQL slow query log logging all queries

  - by Blanka

  We have a MySQL 5.1.52 Percona Server 11.6 instance that suddenly started logging every single query to the slow query log. The long_query_time configuration is set to 1, yet, suddenly we're seeing every single query (e.g. just saw one that took 0.000563s!). As a result, our log files are growing at an insane pace. We just had to truncate a 180G slow query log file. I tried setting the long_query_time variable to a really large number to see if it stopped altogether (1000000), but same result. show global variables like 'general_log%'; +------------------+--------------------------+ | Variable_name | Value | +------------------+--------------------------+ | general_log | OFF | | general_log_file | /usr2/mysql/data/db4.log | +------------------+--------------------------+ 2 rows in set (0.00 sec) show global variables like 'slow_query_log%'; +---------------------------------------+-------------------------------+ | Variable_name | Value | +---------------------------------------+-------------------------------+ | slow_query_log | ON | | slow_query_log_file | /usr2/mysql/data/db4-slow.log | | slow_query_log_microseconds_timestamp | OFF | +---------------------------------------+-------------------------------+ 3 rows in set (0.00 sec) show global variables like 'long%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+ 1 row in set (0.00 sec)

  Read the article

• How can I optimize this subqueried and Joined MySQL Query?

  - by kevzettler

  I'm pretty green on mysql and I need some tips on cleaning up a query. It is used in several variations through out a site. Its got some subquerys derived tables and fun going on. Heres the query: # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 SELECT * FROM ( SELECT products . *, categories.category_name AS category, ( SELECT COUNT( * ) FROM distros WHERE distros.product_id = products.product_id) AS distro_count, (SELECT COUNT(*) FROM downloads WHERE downloads.product_id = products.product_id AND WEEK(downloads.date) = WEEK(curdate())) AS true_downloads, (SELECT COUNT(*) FROM views WHERE views.product_id = products.product_id AND WEEK(views.date) = WEEK(curdate())) AS true_views FROM products INNER JOIN categories ON products.category_id = categories.category_id ORDER BY created_date DESC, true_views DESC ) AS count_table WHERE count_table.distro_count > 0 AND count_table.status = 'published' AND count_table.active = 1 LIMIT 0, 8 Heres the explain: +----+--------------------+------------+-------+---------------+-------------+---------+------------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------+-------+---------------+-------------+---------+------------------------------------+------+----------------------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 232 | Using where | | 2 | DERIVED | categories | index | PRIMARY | idx_name | 47 | NULL | 13 | Using index; Using temporary; Using filesort | | 2 | DERIVED | products | ref | category_id | category_id | 4 | digizald_db.categories.category_id | 9 | | | 5 | DEPENDENT SUBQUERY | views | ref | product_id | product_id | 4 | digizald_db.products.product_id | 46 | Using where | | 4 | DEPENDENT SUBQUERY | downloads | ref | product_id | product_id | 4 | digizald_db.products.product_id | 14 | Using where | | 3 | DEPENDENT SUBQUERY | distros | ref | product_id | product_id | 4 | digizald_db.products.product_id | 1 | Using index | +----+--------------------+------------+-------+---------------+-------------+---------+------------------------------------+------+----------------------------------------------+ 6 rows in set (0.04 sec) And the Tables: mysql> describe products; +---------------+--------------------------------------------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------------------------------------------+------+-----+-------------------+----------------+ | product_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | product_key | char(32) | NO | | NULL | | | title | varchar(150) | NO | | NULL | | | company | varchar(150) | NO | | NULL | | | user_id | int(10) unsigned | NO | MUL | NULL | | | description | text | NO | | NULL | | | video_code | text | NO | | NULL | | | category_id | int(10) unsigned | NO | MUL | NULL | | | price | decimal(10,2) | NO | | NULL | | | quantity | int(10) unsigned | NO | | NULL | | | downloads | int(10) unsigned | NO | | NULL | | | views | int(10) unsigned | NO | | NULL | | | status | enum('pending','published','rejected','removed') | NO | | NULL | | | active | tinyint(1) | NO | | NULL | | | deleted | tinyint(1) | NO | | NULL | | | created_date | datetime | NO | | NULL | | | modified_date | timestamp | NO | | CURRENT_TIMESTAMP | | | scrape_source | varchar(215) | YES | | NULL | | +---------------+--------------------------------------------------+------+-----+-------------------+----------------+ 18 rows in set (0.00 sec) mysql> describe categories -> ; +------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------------+------+-----+---------+----------------+ | category_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | category_name | varchar(45) | NO | MUL | NULL | | | parent_id | int(10) unsigned | YES | MUL | NULL | | | category_type_id | int(10) unsigned | NO | | NULL | | +------------------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> describe compatibilities -> ; +------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------------+------+-----+---------+----------------+ | compatibility_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(45) | NO | | NULL | | | code_name | varchar(45) | NO | | NULL | | | description | varchar(128) | NO | | NULL | | | position | int(10) unsigned | NO | | NULL | | +------------------+------------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec) mysql> describe distros -> ; +------------------+--------------------------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------------------------------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | product_id | int(10) unsigned | NO | MUL | NULL | | | compatibility_id | int(10) unsigned | NO | MUL | NULL | | | user_id | int(10) unsigned | NO | | NULL | | | status | enum('pending','published','rejected','removed') | NO | | NULL | | | distro_type | enum('file','url') | NO | | NULL | | | version | varchar(150) | NO | | NULL | | | filename | varchar(50) | YES | | NULL | | | url | varchar(250) | YES | | NULL | | | virus | enum('READY','PASS','FAIL') | YES | | NULL | | | downloads | int(10) unsigned | NO | | 0 | | +------------------+--------------------------------------------------+------+-----+---------+----------------+ 11 rows in set (0.01 sec) mysql> describe downloads; +------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | product_id | int(10) unsigned | NO | MUL | NULL | | | distro_id | int(10) unsigned | NO | MUL | NULL | | | user_id | int(10) unsigned | NO | MUL | NULL | | | ip_address | varchar(15) | NO | | NULL | | | date | datetime | NO | | NULL | | +------------+------------------+------+-----+---------+----------------+ 6 rows in set (0.01 sec) mysql> describe views -> ; +------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | product_id | int(10) unsigned | NO | MUL | NULL | | | user_id | int(10) unsigned | NO | MUL | NULL | | | ip_address | varchar(15) | NO | | NULL | | | date | datetime | NO | | NULL | | +------------+------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)

  Read the article

• SQL query: Delete a entry which is not present in a join table?

  - by Mestika

  Hi, I’m going to delete all users which has no subscription but I seem to run into problems each time I try to detect the users. My schemas look like this: Users = {userid, name} Subscriptionoffering = {userid, subscriptionname} Now, what I’m going to do is to delete all users in the user table there has a count of zero in the subscriptionoffering table. Or said in other words: All users which userid is not present in the subscriptionoffering table. I’ve tried with different queries but with no result. I’ve tried to say where user.userid <> subscriptionoffering.userid, but that doesn’t seem to work. Do anyone know how to create the correct query? Thanks Mestika

  Read the article

• Slow MySQL Query not using filesort

  - by Canadaka

  I have a query on my homepage that is getting slower and slower as my database table grows larger. tablename = tweets_cache rows = 572,327 this is the query I'm currently using that is slow, over 5 seconds. SELECT * FROM tweets_cache t WHERE t.province='' AND t.mp='0' ORDER BY t.published DESC LIMIT 50; If I take out either the WHERE or the ORDER BY, then the query is super fast 0.016 seconds. I have the following indexes on the tweets_cache table. PRIMARY published mp category province author So i'm not sure why its not using the indexes since mp, provice and published all have indexes? Doing a profile of the query shows that its not using an index to sort the query and is using filesort which is really slow. possible_keys = mp,province Extra = Using where; Using filesort I tried adding a new multie-colum index with "profiles & mp". The explain shows that this new index listed under "possible_keys" and "key", but the query time is unchanged, still over 5 seconds. Here is a screenshot of the profiler info on the query. http://i355.photobucket.com/albums/r469/canadaka_bucket/slow_query_profile.png Something weird, I made a dump of my database to test on my local desktop so i don't screw up the live site. The same query on my local runs super fast, milliseconds. So I copied all the same mysql startup variables from the server to my local to make sure there wasn't some setting that might be causing this. But even after that the local query runs super fast, but the one on the live server is over 5 seconds. My database server is only using around 800MB of the 4GB it has available. here are the related my.ini settings i'm using default-storage-engine = MYISAM max_connections = 800 skip-locking key_buffer = 512M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 4M read_buffer_size = 4M read_rnd_buffer_size = 16M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 128M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 # Disable Federated by default skip-federated key_buffer = 512M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M key_buffer = 512M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M

  Read the article

• Is there anything else I can do to optimize this MySQL query?

  - by Legend

  I have two tables, Table A with 700,000 entries and Table B with 600,000 entries. The structure is as follows: Table A: +-----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------+----------------+ | id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | number | bigint(20) unsigned | YES | | NULL | | +-----------+---------------------+------+-----+---------+----------------+ Table B: +-------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------------+------+-----+---------+----------------+ | id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | number_s | bigint(20) unsigned | YES | MUL | NULL | | | number_e | bigint(20) unsigned | YES | MUL | NULL | | | source | varchar(50) | YES | | NULL | | +-------------+---------------------+------+-----+---------+----------------+ I am trying to find if any of the values in Table A are present in Table B using the following code: $sql = "SELECT number from TableA"; $result = mysql_query($sql) or die(mysql_error()); while($row = mysql_fetch_assoc($result)) { $number = $row['number']; $sql = "SELECT source, count(source) FROM TableB WHERE number_s < $number AND number_e > $number GROUP BY source"; $re = mysql_query($sql) or die(mysql_error); while($ro = mysql_fetch_array($re)) { echo $number."\t".$ro[0]."\t".$ro[1]."\n"; } } I was hoping that the query would go fast but then for some reason, it isn't terrible fast. My explain on the select (with a particular value of "number") gives me the following: mysql> explain SELECT source, count(source) FROM TableB WHERE number_s < 1812194440 AND number_e > 1812194440 GROUP BY source; +----+-------------+------------+------+-------------------------+------+---------+------+--------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+-------------------------+------+---------+------+--------+----------------------------------------------+ | 1 | SIMPLE | TableB | ALL | number_s,number_e | NULL | NULL | NULL | 696325 | Using where; Using temporary; Using filesort | +----+-------------+------------+------+-------------------------+------+---------+------+--------+----------------------------------------------+ 1 row in set (0.00 sec) Is there any optimization that I can squeeze out of this? I tried writing a stored procedure for the same task but it doesn't even seem to work in the first place... It doesn't give any syntax errors... I tried running it for a day and it was still running which felt odd. CREATE PROCEDURE Filter() Begin DECLARE number BIGINT UNSIGNED; DECLARE x INT; DECLARE done INT DEFAULT 0; DECLARE cur1 CURSOR FOR SELECT number FROM TableA; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; CREATE TEMPORARY TABLE IF NOT EXISTS Flags(number bigint unsigned, count int(11)); OPEN cur1; hist_loop: LOOP FETCH cur1 INTO number; SELECT count(*) from TableB WHERE number_s < number AND number_e > number INTO x; IF done = 1 THEN LEAVE hist_loop; END IF; IF x IS NOT NULL AND x>0 THEN INSERT INTO Flags(number, count) VALUES(number, x); END IF; END LOOP hist_loop; CLOSE cur1; END

  Read the article

• Linq-to-sql Compiled Query is returning result from different DataContext

  - by Vladimir Kojic

  Compiled query: public static Func<OperationalDataContext, short, Machine> QueryMachineById = CompiledQuery.Compile((OperationalDataContext db, short machineID) => db.Machines.Where(m => m.MachineID == machineID).SingleOrDefault()); It looks like compiled query is caching Machine object and returning the same object even if query is called from new DataContext (I’m disposing DataContext in the service but I’m getting Machine from previous DataContext). I use POCOs and XML mapping. Revised: It looks like compiled query is returning result from new data context and it is not using the one that I passed in compiled-query. Therefore I can not reuse returned object and link it to another object obtained from datacontext thru non compiled queries. I’m using unit of work pattern : // First Call Using(new DataContext) { Machine from DataContext.Table == machine from cached query } // Do some work // Second Call is failing Using(new DataContext) { Machine from DataContext.Table <> machine from cached query }

  Read the article

• Light-weight, free, database query tool for Windows?

  - by NoCatharsis

  My question is very similar to the one here except pertaining to a Windows tool. I am also referencing this table and what I found here with a Google search. However, I have no idea which tool would best meet my (very basic) purposes. I am currently using Excel with a basic ODBC connection string to query my database at work. However, Excel is pretty memory-heavy and a basic query tends to throw my computer into a 30 second stall-a-thon. Is there a free tool out there that is light-weight and can serve the same purpose when provided an ODBC connection and a SQL query? Also would prefer that it easily copies over to a spreadsheet as needed.

  Read the article

< Previous Page | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >