explain plan - Page 39 - Developer IT

Hello I have this query: SELECT * FROM sample INNER JOIN test ON sample.sample_number = test.sample_number INNER JOIN result ON test.test_number = result.test_number WHERE sampled_date BETWEEN '2010-03-17 09:00' AND '2010-03-17 12:00' the biggest table here is RESULT, contains 11.1M records. The left 2 tables about 1M. this query works slowly (more than 10 minutes) and returns about 800 records. executing plan shows clustered index scan (over it's PRIMARY KEY (result.result_number, which actually doesn't take part in query)) over all 11M records. RESULT.TEST_NUMBER is a clustered primary key. if I change 2010-03-17 09:00 to 2010-03-17 10:00 - i get about 40 records. it executes for 300ms. and plan shows index seek (over result.test_number index) if i replace * in SELECT clause to result.test_number (covered with index) - then all become fast in first case too. this points to hdd IO issues, but doesn't clarifies changing plan. so, any ideas? UPDATE: sampled_date is in table sample and covered by index. other fields from this query: test.sample_number is covered by index and result.test_number too. UPDATE 2: obviously than sql server in any reasons don't want to use index. i did a small experiment: i remove INNER JOIN with result, select all test.test_number and after that do SELECT * FROM RESULT WHERE TEST_NUMBER IN (...) this, of course, works fast. but i cannot get what is the difference and why query optimizer choose such inappropriate way to select data in 1st case.

Read the article

Using a .MDF SQL Server Database with ASP.NET Versus Using SQL Server

- by Maxim Z.

I'm currently writing a website in ASP.NET MVC, and my database (which doesn't have any data in it yet, it only has the correct tables) uses SQL Server 2008, which I have installed on my development machine. I connect to the database out of my application by using the Server Explorer, followed by LINQ to SQL mapping. Once I finish developing the site, I will move it over to my hosting service, which is a virtual hosting plan. I'm concerned about whether using the SQL Server setup that is currently working on my development machine will be hard to do on the production server, as I'll have to import all the database tables through the hosting control panel. I've noticed that it is possible to create a SQL Server database from inside Visual Studio. It is then stored in the App_Data directory. My questions are the following: Does it make sense to move my SQL Server DB out of SQL Server and into the App_Data directory as an .mdf file? If so, how can I move it? I believe this is called the Detach command, is it not? Are there any performance/security issues that can occur with a .mdf file like this? Would my intended setup work OK with a typical virtual hosting plan? I'm hoping that the .mdf database won't count against the limited number of SQL Server databases that can be created with my plan. I hope this question isn't too broad. Thanks in advance! Note: I'm just starting out with ASP.NET MVC and all this, so I might be completely misunderstanding how this is supposed to work.

Read the article

Why is MySQL with InnoDB doing a table scan when key exists and choosing to examine 70 times more ro

- by andysk

Read the article

LINQ Joins - Performance

- by Meiscooldude

I am curious on how exactly LINQ (not LINQ to SQL) is performing is joins behind the scenes in relation to how Sql Server performs joins. Sql Server before executing a query, generates an Execution Plan. The Execution Plan is basically an Expression Tree on what it believes is the best way to execute the query. Each node provides information on whether to do a Sort, Scan, Select, Join, ect. On a 'Join' node in our execution plan, we can see three possible algorithms; Hash Join, Merge Join, and Nested Loops Join. Sql Server will choose which algorithm to for each Join operation based on expected number of rows in Inner and Outer tables, what type of join we are doing (some algorithms don't support all types of joins), whether we need data ordered, and probably many other factors. Join Algorithms: Nested Loop Join: Best for small inputs, can be optimized with ordered inner table. Merge Join: Best for medium to large inputs sorted inputs, or an output that needs to be ordered. Hash Join: Best for medium to large inputs, can be parallelized to scale linearly. LINQ Query: DataTable firstTable, secondTable; ... var rows = from firstRow in firstTable.AsEnumerable () join secondRow in secondTable.AsEnumerable () on firstRow.Field<object> (randomObject.Property) equals secondRow.Field<object> (randomObject.Property) select new {firstRow, secondRow}; SQL Query: SELECT * FROM firstTable fT INNER JOIN secondTable sT ON fT.Property = sT.Property Sql Server might use a Nested Loop Join if it knows there are a small number of rows from each table, a merge join if it knows one of the tables has an index, and Hash join if it knows there are a lot of rows on either table and neither has an index. Does Linq choose its algorithm for joins? or does it always use one?

Read the article

SQL server 2005 agent not working

- by flaggers

Sql server 2005 service pack 2 version: 9.00.3042.00 All maintenance plans fail with the same error. The details of the error are:- Execute Maintenance Plan Execute maintenance plan. test7 (Error) Messages Execution failed. See the maintenance plan and SQL Server Agent job history logs for details. The advanced information section shows the following; Job 'test7.Subplan_1' failed. (SqlManagerUI) Program Location: at Microsoft.SqlServer.Management.SqlManagerUI.MaintenancePlanMenu_Run.PerformActions() At this point the following appear in the windows event log: Event Type: Error Event Source: SQLISPackage Event Category: None Event ID: 12291 Date: 28/05/2009 Time: 16:09:08 User: 'DOMAINNAME\username' Computer: SQLSERVER4 Description: Package "test7" failed. and also this: Event Type: Warning Event Source: SQLSERVERAGENT Event Category: Job Engine Event ID: 208 Date: 28/05/2009 Time: 16:09:10 User: N/A Computer: SQLSERVER4 Description: SQL Server Scheduled Job 'test7.Subplan_1' (0x96AE7493BFF39F4FBBAE034AB6DA1C1F) - Status: Failed - Invoked on: 2009-05-28 16:09:02 - Message: The job failed. The Job was invoked by User 'DOMAINNAME\username'. The last step to run was step 1 (Subplan_1). There are no entries in the SQl Agent log at all.

Read the article

What noncluster index would be better to create on SQL Server?

- by Junior Mayhé

Here I am studying nonclustered indexes on SQL Server Management Studio. I've created a table with more than 1 million records. This table has a primary key. SELECT CustomerName FROM Customers Which leads the execution plan to show me: I/O cost = 3.45646 Operator cost = 4.57715 For the first attempt to improve performance, I've created a nonclustered index for this table: CREATE NONCLUSTERED INDEX [IX_CustomerID_CustomerName] ON [dbo].[Customers] ( [CustomerId] ASC, [CustomerName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO With this first try, I've executed the select statement and the execution plan shows me: I/O cost = 2.79942 Operator cost = 3.92001 Now the second try, I've deleted this nonclustered index in order to create a new one. CREATE NONCLUSTERED INDEX [IX_CategoryName] ON [dbo].[Categories] ( [CategoryId] ASC ) INCLUDE ( [CategoryName]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO With this second try, I've executed the select statement and the execution plan shows me the same result: I/O cost = 2.79942 Operator cost = 3.92001 Am I doing something wrong or this is expected? Shall I use the first nonclustered index with two fields, or the second nonclustered with one field (CategoryID) including the second field (CategoryName)?

Read the article

Need a workaround to filter on related model and aggregated fields in Django

- by parxier

I opened a ticket for this problem. In a nutshell here is my model: class Plan(models.Model): cap = models.IntegerField() class Phone(models.Model): plan = models.ForeignKey(Plan, related_name='phones') class Call(models.Model): phone = models.ForeignKey(Phone, related_name='calls') cost = models.IntegerField() I want to run a query like this one: Phone.objects.annotate(total_cost=Sum('calls__cost')).filter(total_cost__gte=0.5*F('plan__cap')) Unfortunately Django generates bad SQL: SELECT "app_phone"."id", "app_phone"."plan_id", SUM("app_call"."cost") AS "total_cost" FROM "app_phone" INNER JOIN "app_plan" ON ("app_phone"."plan_id" = "app_plan"."id") LEFT OUTER JOIN "app_call" ON ("app_phone"."id" = "app_call"."phone_id") GROUP BY "app_phone"."id", "app_phone"."plan_id" HAVING SUM("app_call"."cost") >= 0.5 * "app_plan"."cap" and errors with: ProgrammingError: column "app_plan.cap" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...."plan_id" HAVING SUM("app_call"."cost") >= 0.5 * "app_plan".... Is there any workaround apart from running raw SQL?

Read the article

How do I implement aasm in Rails 3 for what I want it to do?

- by marcamillion

I am a Rails n00b and have been advised that in order for me to keep track of the status of my user's accounts (i.e. paid, unpaid (and therefore disabled), free trial, etc.) I should use an 'AASM' gem. So I found one that seems to be the most popular: https://github.com/rubyist/aasm But the instructions are pretty vague. I have a Users model and a Plan model. User's model manages everything you might expect (username, password, first name, etc.). Plan model manages the subscription plan that users should be assigned to (with the restrictions). So I am trying to figure out how to use the AASM gem to do what I want to do, but no clue where to start. Do I create a new model ? Then do I setup a relationship between my User model and the model for AASM ? How do I setup a relationship? As in, a user 'has_many' states ? That doesn't seem to make much sense to me. Any guidance would be really appreciated. Thanks. Edit: If anyone else is confused by AASMs like myself, here is a nice explanation of their function in Rails by the fine folks at Envy Labs: http://blog.envylabs.com/2009/08/the-rails-state-machine/ Edit2: How does this look: include AASM aasm_column :current_state aasm_state :paid aasm_state :free_trial aasm_state :disabled #this is for accounts that have exceed free trial and have not paid #aasm_state :free_acct aasm_event :pay do transitions :to => :paid, :from => [:free_trial, :disabled] transitions :to => :disabled, :from => [:free_trial, :paid] end

Read the article

Using h:outputFormat to message-format the f:selectItems of a h:selectOneRadio

- by msharma

I am having some trouble with using h:selectOneRadio. I have a list of objects which is being returned which needs to be displayed. I am trying something like this: <h:selectOneRadio id="selectPlan" layout="pageDirection"> <f:selectItems value="#{detailsHandler.planList}" /> </h:selectOneRadio> and planList is a List of Plans. Plan is defined as: public class Plan { protected String id; protected String partNumber; protected String shortName; protected String price; protected boolean isService; protected boolean isOption; //With all getters/setters } The text that must appear for each radio button is actually in a properties file, and I need to insert params in the text to fill out some value in the bean. For example the text in my properties file is: plan_price=The price of this plan is {0}. I was hoping to do something like this: <f:selectItems value="<h:outputFormat value="#{i18n.plan_price}"> <f:param value="#{planHandler.price}"> </h:outputFormat>" /> Usually if it's not a h:selectOneRadio component, if it's just text I use the h:outputFormat along with f:param tags to display the messages in my .property file called i18n above, and insert a param which is in the backing bean. here this does not work. Does anyone have any ideas how I can deal with this? I am being returned a list of Plans each with their own prices and the text to be displayed is held in property file. Any help much appreciated. Thanks!

Read the article

Sql Server 2005 Check Constraint not being applied in execution when using variables

- by DarylS

Here is some SQL sample code: --Create 2 Sales tables with constraints based on the saledate create table Sales1(SaleDate datetime, Amount money) ALTER TABLE dbo.Sales1 ADD CONSTRAINT CK_Sales1 CHECK (([SaleDate]>='01 May 2010')) GO create table Sales2(SaleDate datetime, Amount money) ALTER TABLE dbo.Sales2 ADD CONSTRAINT CK_Sales2 CHECK (([SaleDate]<'01 May 2010')) GO --Insert some data into Sales1 insert into Sales1 (SaleDate, Amount) values ('02 May 2010', 50) insert into Sales1 (SaleDate, Amount) values ('03 May 2010', 60) GO --Insert some data into Sales2 insert into Sales2 (SaleDate, Amount) values ('30 Mar 2010', 10) insert into Sales2 (SaleDate, Amount) values ('31 Mar 2010', 20) GO --Create a view that combines these 2 tables create VIEW [dbo].[Sales] AS SELECT SaleDate, Amount FROM Sales1 UNION ALL SELECT SaleDate, Amount FROM Sales2 GO --Get the results --Query 1 select * from Sales where SaleDate < '31 Mar 2010' -- if you look at the execution plan this query only looks at Sales2 (Which is good) --Query 2 DECLARE @SaleDate datetime SET @SaleDate = '31 Mar 2010' select * from Sales where SaleDate < @SaleDate -- if you look at the execution plan this query looks at Sales1 and Sales2 (Which is NOT good) Looking at the execution plan you will see that the two queries are differnt. For Query 1 the only table that is accessed is Sales1 (which is good). For Query 2 both tables are accessed (Which is bad). Why are these execution plans different, and how do i get Query 2 to only access the relevant table when variables are used? I have tried to add indexes for the SaleDate column and that does not seem to help.

Read the article

Anything like Heroku for PHP or .NET?

- by Wayne M

In my area PHP is very widespread, so is .NET. Ruby not so much; most places have never heard of it. For some personal things I am "forced" to choose Rails because I want to take advantage of Heroku - the ability to deploy and scale on the cloud very easily is the main reason. Also, they offer a small FREE plan that I can use for demo sites or, in this case, for my business' static page; as a totally bootstrapped startup I have maybe $50 or so in initial capital and cannot afford to pay monthly fees while I'm getting started. Are there any similar offerings for other languages? Specifically, I really like the small, 5MB site for free that Heroku offers - is there anything like that for PHP and/or .NET? I'm not even that concerned about the "cloud" part, but that would be a nice bonus. If there is, I might be able to kill two birds with one stone and pick up a useful skill as I'm doing my own thing instead of using something that nobody else knows or cares about. I should add I'm specifically interested in something that offers a free plan. As I said, Heroku has a 5mb plan that you can have as many as you want for free; I have yet to find anything similar for any other platform, and to be honest I'm not too thrilled about using Ruby on Rails for everything simply to take advantage of this.

Read the article

How to handle ids and polymorphic associations in views if compound keys are not supported?

- by duncan

I have a Movie plan table: movie_plans (id, description) Each plan has items, which describe a sequence of movies and the duration in minutes: movie_plan_items (id, movie_plan_id, movie_id, start_minutes, end_minutes) A specific instance of that plan happens in: movie_schedules (id, movie_plan_id, start_at) However the schedule items can be calculated from the movie_plan_items and the schedule start time by adding the minutes create view movie_schedule_items as select CONCAT(p.id, '-', s.id) as id, s.id as movie_schedule_id, p.id as movie_plan_item_id, p.movie_id, p.movie_plan_id, (s.start_at + INTERVAL p.start_minutes MINUTE) as start_at, (s.start_at + INTERVAL p.end_minutes MINUTE) as end_at from movie_plan_items p, movie_schedules s where s.movie_plan_id=p.movie_plan_id; I have a model over this view (readonly), it works ok, except that the id is right now a string. I now want to add a polymorphic property (like comments) to various of the previous tables. Therefore for movie_schedule_items I need a unique and persistent numeric id. I have the following dilemma: I could avoid the id and have movie_schedule_items just use the movie_plan_id and movie_schedule_id as a compound key, as it should. But Rails sucks in this regard. I could create an id using String#hash or a md5, thus making it slower or collision prone (and IIRC String#hash is no longer persistent across processes in Ruby 1.9) Any ideas on how to handle this situation?

Search Results

Search found 11015 results on 441 pages for 'explain plan'.

Page 39/441 | < Previous Page | 35 36 37 38 39 40 41 42 43 44 45 46 | Next Page >

- by zerkms

- by Maxim Z.

- by andysk

- by Meiscooldude

- by flaggers

- by Junior Mayhé

- by parxier

- by marcamillion

- by msharma

- by DarylS

- by Wayne M

- by duncan

< Previous Page | 35 36 37 38 39 40 41 42 43 44 45 46 | Next Page >