Search Results

Search found 17627 results on 706 pages for 'hierarchical query'.

Page 214/706 | < Previous Page | 210 211 212 213 214 215 216 217 218 219 220 221  | Next Page >

• How to optimize this SQL query for a rectangular region?

  - by Andrew B.

  I'm trying to optimize the following query, but it's not clear to me what index or indexes would be best. I'm storing tiles in a two-dimensional plane and querying for rectangular regions of that plane. The table has, for the purposes of this question, the following columns: id: a primary key integer world_id: an integer foreign key which acts as a namespace for a subset of tiles tileY: the Y-coordinate integer tileX: the X-coordinate integer value: the contents of this tile, a varchar if it matters. I have the following indexes: "ywot_tile_pkey" PRIMARY KEY, btree (id) "ywot_tile_world_id_key" UNIQUE, btree (world_id, "tileY", "tileX") "ywot_tile_world_id" btree (world_id) And this is the query I'm trying to optimize: ywot=> EXPLAIN ANALYZE SELECT * FROM "ywot_tile" WHERE ("world_id" = 27685 AND "tileY" <= 6 AND "tileX" <= 9 AND "tileX" >= -2 AND "tileY" >= -1 ); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ywot_tile (cost=11384.13..149421.27 rows=65989 width=168) (actual time=79.646..80.075 rows=96 loops=1) Recheck Cond: ((world_id = 27685) AND ("tileY" <= 6) AND ("tileY" >= (-1)) AND ("tileX" <= 9) AND ("tileX" >= (-2))) -> Bitmap Index Scan on ywot_tile_world_id_key (cost=0.00..11367.63 rows=65989 width=0) (actual time=79.615..79.615 rows=125 loops=1) Index Cond: ((world_id = 27685) AND ("tileY" <= 6) AND ("tileY" >= (-1)) AND ("tileX" <= 9) AND ("tileX" >= (-2))) Total runtime: 80.194 ms So the world is fixed, and we are querying for a rectangular region of tiles. Some more information that might be relevant: All the tiles for a queried region may or may not be present The height and width of a queried rectangle are typically about 10x10-20x20 For any given (world, X) or (world, Y) pair, there may be an unbounded number of matching tiles, but the worst case is currently around 10,000, and typically there are far fewer. New tiles are created far less frequently than existing ones are updated (changing the 'value'), and that itself is far less frequent that just reading as in the query above. The only thing I can think of would be to index on (world, X) and (world, Y). My guess is that the database would be able to take those two sets and intersect them. The problem is that there is a potentially unbounded number of matches for either for either of those. Is there some other kind of index that would be more appropriate?

  Read the article

• Does the speed of the query depend on the number of rows in the table?

  - by Omu

  Let's say I have this query: select * from table1 r where r.x = 5 Does the speed of this query depend on the number of rows that are present in table1?

  Read the article

• SQL Query Syntax : Using table alias in a count is invalid? Why?

  - by contactmatt

  Could someone please explain to me why the following query is invalid? I'm running this query against an Oracle 10g database. select count(test.*) from my_table test; I get the following error: ORA-01747: invalid user.table.column, table.column, or column specification however, the following two queries are valid. select count(test.column) from my_table test; select test.* from my_table test;

  Read the article

• LINQ Query returns false when it should be true.

  - by deliriousDev

  I have the following LINQ query written by a former developer and it isn't working when it should. public bool IsAvailable(Appointment appointment) { var appointments = _appointmentRepository.Get; var shifts = _scheduleRepository.Get; var city = _customerRepository.Find(appointment.CustomerId).City ?? appointment.Customer.City; const int durationHour = 1; DateTime scheduledEndDate = appointment.ScheduledTime.Add(new TimeSpan(durationHour, 0, 0)); var inWorkingHours = shifts .Where(x => //Check if any available working hours x.Employee.City == city && x.ShiftStart <= appointment.ScheduledTime && x.ShiftEnd >= scheduledEndDate && //check if not booked yet !appointments .Where(a => (appointment.Id == 0 || a.Id != appointment.Id) && a.Employee.Id == x.Employee.Id && ( (a.ScheduledTime <= appointment.ScheduledTime && appointment.ScheduledTime <= EntityFunctions.AddHours(a.ScheduledTime, durationHour)) || (a.ScheduledTime <= scheduledEndDate && scheduledEndDate <= EntityFunctions.AddHours(a.ScheduledTime, durationHour)) )) .Select(a => a.Employee.Id) .Contains(x.Employee.Id) ); if (inWorkingHours.Any()) { var assignedEmployee = inWorkingHours.FirstOrDefault().Employee; appointment.EmployeeId = assignedEmployee.Id; appointment.Employee = assignedEmployee; return true; } return false; } The query is suppose to handle the following scenarios Given An Appointment With A ScheduledTime Between A ShiftStart and ShiftEnd time But Does not match any employees in same city - (Return true, Assign as "Unassigned") Given An Appointment With A ScheduledTime Between A ShiftStart and ShiftEnd time AND Employee for that shift is in the same city as the customer (Return True AND Assign to the employee) If the customer is NOT in the same city as an employee we assign the appointment as "Unassigned" as along as the scheduledTime is within an of the employees shift start/end times If the customer is in the same city as an employee we assign the appointment to one of the employees (firstOrdefault) and occupy that timeslot. Appointments CAN NOT overlap (Assigned Ones). Unassigned can't overlap each other. This query use to work (I've been told). But now it doesn't and I have tried refactoring it and various other paths with no luck. I am now on week two and just don't know where the issue in the query is or how to write it. Let me know if I need to post anything further. I have verified appointments, shifts, city all populate with valid data so the issue doesn't appear to be with null or missing data.

  Read the article

• How to select all parent objects into DataContext using single LINQ query ?

  - by too

  I am looking for an answer to a specific problem of fetching whole LINQ object hierarchy using single SELECT. At first I was trying to fill as much LINQ objects as possible using LoadOptions, but AFAIK this method allows only single table to be linked in one query using LoadWith. So I have invented a solution to forcibly set all parent objects of entity which of list is to be fetched, although there is a problem of multiple SELECTS going to database - a single query results in two SELECTS with the same parameters in the same LINQ context. For this question I have simplified this query to popular invoice example: public static class Extensions { public static IEnumerable<T> ForEach<T>(this IEnumerable<T> collection, Action<T> func) { foreach(var c in collection) { func(c); } return collection; } } public IEnumerable<Entry> GetResults(AppDataContext context, int CustomerId) { return ( from entry in context.Entries join invoice in context.Invoices on entry.EntryInvoiceId equals invoice.InvoiceId join period in context.Periods on invoice.InvoicePeriodId equals period.PeriodId // LEFT OUTER JOIN, store is not mandatory join store in context.Stores on entry.EntryStoreId equals store.StoreId into condStore from store in condStore.DefaultIfEmpty() where (invoice.InvoiceCustomerId = CustomerId) orderby entry.EntryPrice descending select new { Entry = entry, Invoice = invoice, Period = period, Store = store } ).ForEach(x => { x.Entry.Invoice = Invoice; x.Invoice.Period = Period; x.Entry.Store = Store; } ).Select(x => x.Entry); } When calling this function and traversing through result set, for example: var entries = GetResults(this.Context); int withoutStore = 0; foreach(var k in entries) { if(k.EntryStoreId == null) withoutStore++; } the resulting query to database looks like (single result is fetched): SELECT [t0].[EntryId], [t0].[EntryInvoiceId], [t0].[EntryStoreId], [t0].[EntryProductId], [t0].[EntryQuantity], [t0].[EntryPrice], [t1].[InvoiceId], [t1].[InvoiceCustomerId], [t1].[InvoiceDate], [t1].[InvoicePeriodId], [t2].[PeriodId], [t2].[PeriodName], [t2].[PeriodDateFrom], [t4].[StoreId], [t4].[StoreName] FROM [Entry] AS [t0] INNER JOIN [Invoice] AS [t1] ON [t0].[EntryInvoiceId] = [t1].[InvoiceId] INNER JOIN [Period] AS [t2] ON [t2].[PeriodId] = [t1].[InvoicePeriodId] LEFT OUTER JOIN ( SELECT 1 AS [test], [t3].[StoreId], [t3].[StoreName] FROM [Store] AS [t3] ) AS [t4] ON [t4].[StoreId] = ([t0].[EntryStoreId]) WHERE (([t1].[InvoiceCustomerId]) = @p0) ORDER BY [t0].[InvoicePrice] DESC -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [186] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1 SELECT [t0].[EntryId], [t0].[EntryInvoiceId], [t0].[EntryStoreId], [t0].[EntryProductId], [t0].[EntryQuantity], [t0].[EntryPrice], [t1].[InvoiceId], [t1].[InvoiceCustomerId], [t1].[InvoiceDate], [t1].[InvoicePeriodId], [t2].[PeriodId], [t2].[PeriodName], [t2].[PeriodDateFrom], [t4].[StoreId], [t4].[StoreName] FROM [Entry] AS [t0] INNER JOIN [Invoice] AS [t1] ON [t0].[EntryInvoiceId] = [t1].[InvoiceId] INNER JOIN [Period] AS [t2] ON [t2].[PeriodId] = [t1].[InvoicePeriodId] LEFT OUTER JOIN ( SELECT 1 AS [test], [t3].[StoreId], [t3].[StoreName] FROM [Store] AS [t3] ) AS [t4] ON [t4].[StoreId] = ([t0].[EntryStoreId]) WHERE (([t1].[InvoiceCustomerId]) = @p0) ORDER BY [t0].[InvoicePrice] DESC -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [186] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1 The question is why there are two queries and how can I fetch LINQ objects without such hacks?

  Read the article

• .NET: Is there a way to finagle a default namespace in an XPath 1.0 query?

  - by Cheeso

  I'm building a tool that performs xpath 1.0 queries on XHTML documents. The requirement to use a namespace prefix in the query is killing me. The query looks like this: html/body/div[@class='contents']/div[@class='body']/ div[@class='pgdbbyauthor']/h2[a[@name][starts-with(.,'Quick')]]/ following-sibling::ul[1]/li/a (all on one line) ...which is bad enough, except because it's xpath 1.0, I need to use an explicit namespace prefix on each QName, so it looks like this: ns1:html/ns1:body/ns1:div[@class='contents']/ns1:div[@class='body']/ ns1:div[@class='pgdbbyauthor']/ns1:h2[ns1:a[@name][starts-with(.,'Quick')]]/ following-sibling::ns1:ul[1]/ns1:li/ns1:a To set up the query, I do something like this: var xpathDoc = new XPathDocument(new StringReader(theText)); var nav = xpathDoc.CreateNavigator(); var xmlns = new XmlNamespaceManager(nav.NameTable); foreach (string prefix in xmlNamespaces.Keys) xmlns.AddNamespace(prefix, xmlNamespaces[prefix]); XPathNodeIterator selection = nav.Select(xpathExpression, xmlns); But what I want is for the xpathExpression to use the implicit default namespace. Is there a way for me to transform the unadorned xpath expression, after it's been written, to inject a namespace prefix for each element name in the query? I'm thinking, anything between two slashes, I could inject a prefix there. Excepting of course axis names like "parent::" and "preceding-sibling::" . And wildcards. That's what I mean by "finagle a default namespace". Is this hack gonna work? Addendum Here's what I mean. suppose I have an xpath expression, and before passing it to nav.Select(), I transform it. Something like this: string FixupWithDefaultNamespace(string expr) { string s = expr; s = Regex.Replace(s, "^(?!::)([^/:]+)(?=/)", "ns1:$1"); // beginning s = Regex.Replace(s, "/([^/:]+)(?=/)", "/ns1:$1"); // stanza s = Regex.Replace(s, "::([A-Za-z][^/:*]*)(?=/)", "::ns1:$1"); // axis specifier s = Regex.Replace(s, "\\[([A-Za-z][^/:*\\(]*)(?=[\\[\\]])", "[ns1:$1"); // predicate s = Regex.Replace(s, "/([A-Za-z][^/:]*)(?!<::)$", "/ns1:$1"); // end s = Regex.Replace(s, "^([A-Za-z][^/:]*)$", "ns1:$1"); // edge case s = Regex.Replace(s, "([-A-Za-z]+)\\(([^/:\\.,\\)]+)(?=[,\\)])", "$1(ns1:$2"); // xpath functions return s; } This actually works for simple cases I tried. To use the example from above - if the input is the first xpath expression, the output I get is the 2nd one, with all the ns1 prefixes. The real question is, is it hopeless to expect this Regex.Replace approach to work, as the xpath expressions get more complicated?

  Read the article

• How would I change this Query builder from Kohana 2.4 to Kohana 3?

  - by Thorpe Obazee

  I have had this website built for a few months and I am just getting on Kohana 3. I'd just like to convert this K2.4 query builder to K3 query builder. return DB::select(array('posts.id', 'posts.created', 'posts.uri', 'posts.price', 'posts.description', 'posts.title', 'image_count' => db::expr('COUNT(images.id)'))) ->from('posts') ->join('images')->on('images.post_id', '=', 'posts.id') ->group_by(array('posts.id')) ->order_by('posts.id', 'DESC') ->limit($limit) ->offset($offset) ->execute();

  Read the article

• how to run Make table query that i have in access in C# code ?

  - by Gold

  hi i have query (DO2) in access that makes a table and inserting any data. how to run this query in C# code ? i try this: SQL = "Select * from DO2"; Cmd = new OleDbCommand(SQL, Conn); Cmd.ExecuteNonQuery(); Cmd.Dispose(); but i get error

  Read the article

• Do the speed of the query depends on the number of rows in the table ?

  - by Omu

  let's say I have this query: select * from table1 r where r.x = 5 do the speed of this query depends on the number of rows that are present in table1 ?

  Read the article

• Is there a way that i can read from mysql binarylog file with php code and insert query statement in

  - by mohammad mirzadeh

  I want insert query statement in DB for LOG. Is there a way that i can read from mysql binarylog file with php code and insert query statement in DB?

  Read the article

• In an MS Access Maketable query, how to create an Autonumber field?

  - by PowerUser

  I have a Maketable query in an Access db that could use an Autonumber field. I can't find a built-in function for my purpose. Do i need to write my own? Ideally, I'd just like to create a field in the Access designer as "Autonum: CreateAutoNumber()" Edit: If it can't be done in the query itself, I can also run a procedure afterward.

  Read the article

• how to store a value returned from a sql query in a variable in batch programming ?

  - by Arunachalam

  how to store a value returned from a sql query in a variable in batch programming ? i can invoke sqlserver queries from my cmd prompt using sqlcmd server name then the qwery this is query statement i m going to use SELECT CASE WHEN DATEDIFF(minute, record_timestamp, GETDATE()) < 10 THEN 1 ELSE 0 END how to store the value returned i tried using set variablename but it save the statement rather than the return value .. and if i save this in a variable what type of variable it will can i compare it with numeric values in if condition

  Read the article

• What is a 'better' approach to query/save from server: DTO or Wcf Data Services?

  - by bonefisher

  From my perspective, the Data Services and their query approach is useful when querying simple object graphs from your server-side domain model. But when you want to query complex dependencies I couldn't create anything good out of it. The classic DTO approach is fine-grained and can handle everything, but the downside is that you have to create Dto classes for every type of server-request which is time consuming and you have to synchronize the Dto type with your domain entity/business logic.

  Read the article

• Using Jquery .ajax method how can i read the query parameter and hit the web method and get the resu

  - by ricky roy

  hi, I am using Asp.net with jquery I wanted to display information for a specific product that is getting from the query string so my question is How can i get the query string through jquery and hit the Web method and get the result in json and display that result on my page on page load. Thanks waiting for your replay..

  Read the article

• How do I get results from a Linq query in the order of IDs that I provide?

  - by Keltex

  I'm looking to get query results back from Linq in the order that I pass IDs to the query. So it would look something like this: var IDs = new int [] { 5, 20, 10 } var items = from mytable in db.MyTable where IDs.Contains(mytable.mytableID) orderby // not sure what to do here select mytable; I'm hoping to get items in the order of IDs (5, 20, 10). (Note this is similar to this question, but I would like to do it in Linq instead of SQL)

  Read the article

• In Visual Studio 2008, Right-Click on a Data Connection in Server Explorer > New Query, Can I change

  - by Jon Erickson

  I hate the standard Query window that comes up when you select new query on a data connection in server explorer, I'd much rather have a T-SQL Editor come up. Is this possible? Maybe not replace that functionality but a shortcut key or something that would bring up a T-SQL editor?

  Read the article

• Can I write this query using the criteria API or am I stuck with HQL?

  - by Yannis

  hi all. I have the following query which I would like to write using the criteria api of NH. select status, count(1) from (select distinct Status, post_id from post_statistics) tbl group by status each post_id can exist multiple times in post_statistics e.g. id post_id status 1 1 open 1 1 edit 1 1 open 1 2 open so the query should return the following results: status count open 2 edit 1 thx in advance.

  Read the article

• how to get messages in the inbox of facebook using fql query?

  - by Ershad

  When i request for inbox messages in fql.query using the below query. SELECT message_count FROM thread WHERE folder_id = 0 It is giving this error. yyyyyyyyyy is not permitted to requested mailbox permissions from xxxxxxxxx

  Read the article

• What is the most efficient way to populate class attributes with a row from a database query?

  - by ensnare

  Looking to have a database query set all the instance variables in a class: Example: def populate(self, if): #Perform mysql query self._name = row['name'] self._email = row['email'] ... What's the fastest way to do this? Or is this not recommended (with a better approach)? Thanks.

  Read the article

• How do I execute a stored procedure once for each row returned by query?

  - by Ryan

  I have a stored procedure that alters user data in a certain way. I pass it user_id and it does it's thing. I want to run a query on a table and then for each user_id I find run the stored procedure once on that user_id How would I write query for this? SQL SERVER

  Read the article

• What version of mongodb was full $text query operator introduced?

  - by Marc Maxson

  Stupid question, right? But the official docs for 'text index' say: http://docs.mongodb.org/manual/core/index-text/ Text Indexes New in version 2.4. To perform queries that access the text index, use the $text query operator. Whereas if you click on the help for searching the index you created with the $text operator, it reads: http://docs.mongodb.org/manual/reference/operator/query/text/#op._S_text $text New in version 2.6. Seems to be 2.4 but still having problems wiht it.

  Read the article

• how to execute Sql Raw Query in Microsoft Entity Model Framework.

  - by kamal

  Hi I'm using Microsoft Entity model framework to access my database. I get an issue while i use this execute query command for executing Sql raw query. Let me know how could i resolve it. svdc.CreateQuery("select * from videomastertable WHERE FREETEXT(*, '"+keyword+"')").ToList(); Thanks in Advance, Kanal

  Read the article

• Any way to make this PostgreSQL count query any faster?

  - by Ben Dauphinee

  I'm running a case-insensitive search on a table with 7.2 million rows, and I was wondering if there was any way to make this query any faster? Currently, it takes approx 11.6 seconds to execute, with just one search parameter, and I'm worried that as soon as I add more than one, this query will become massively slow. SELECT count(*) FROM "exif_parse" WHERE (description ~* 'canon')

  Read the article

• Where does the query language sit within the MVC pattern?

  - by weesilmania

  I'd assume that since the query language sits within the controller (typically) that it belongs to that component, but if I play devil's advocate I'd argue that the query language is execute within the domain of the model, and is tightly coupled to that component so it might also be a part of it. Anyone know the answer? Is there a straight answer or is it technology specific?

  Read the article

• PL/SQL 'select in' from a list of values whose type are different from the outer query

  - by Attilah

  I have the following tables : Table1 ( Col1 : varchar2, Col2 : number, Col3 : number) Table2 ( Col1 : number, Col2 : varchar2, Col3 : varchar2) I want to run a query like this : select distinct Col2 from Table1 where Col1 in ( select Col1 from Table2 ) Table1.Col1 is of type varchar2 while Table2.Col1 is of type number. so, I need to do some casting, it seems but it fails to succeed. The problem is that any attempts to run the query returns the following error : ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: *Action: Table1.Col1 contains some null values.

  Read the article

< Previous Page | 210 211 212 213 214 215 216 217 218 219 220 221  | Next Page >