Search Results

Search found 28469 results on 1139 pages for 'select statement'.

Page 334/1139 | < Previous Page | 330 331 332 333 334 335 336 337 338 339 340 341  | Next Page >

  • Preset value of HTML Forms - iPhone SDK

    - by laura
    Hi. I'm trying to preset the value of a dropdown menu and the value of a text box in an HTML form. As soon as the user taps the button in my iPhone app, it enters the webview and I was hoping to preset one of the dropdown menus and text field. How do I go about this? I want to set the dropdown to value "4" and the text field to "Giro Apps" Here's the relevant HTML form code: <select id="advSrcId" class="list" onchange="showOther($('advSrcId').options[$('advSrcId').selectedIndex].value)" name="advSrcId"> <option selected="" value="">Select</option> <option value="1">Google</option> <option value="2">Facebook</option> <option value="3">Friend Referral</option> <option value="4">Other</option> </select> <div id="otherAdvsrc" style=""> <p> Please give details here: <br/> <input class="text" type="text" value="" name="advsrc"/> <br/> </p> </div> TIA!

    Read the article

  • Apply a recursive CTE on grouped table rows (SQL server 2005).

    - by Evan V.
    Hi all, I have a table (ROOMUSAGE) containing the times people check in and out of rooms grouped by PERSONKEY and ROOMKEY. It looks like this: PERSONKEY | ROOMKEY | CHECKIN | CHECKOUT | ROW ---------------------------------------------------------------- 1 | 8 | 13-4-2010 10:00 | 13-4-2010 11:00 | 1 1 | 8 | 13-4-2010 08:00 | 13-4-2010 09:00 | 2 1 | 1 | 13-4-2010 15:00 | 13-4-2010 16:00 | 1 1 | 1 | 13-4-2010 14:00 | 13-4-2010 15:00 | 2 1 | 1 | 13-4-2010 13:00 | 13-4-2010 14:00 | 3 13 | 2 | 13-4-2010 15:00 | 13-4-2010 16:00 | 1 13 | 2 | 13-4-2010 15:00 | 13-4-2010 16:00 | 2 I want to select just the consecutive rows for each PERSONKEY, ROOMKEY grouping. So the desired resulting table is: PERSONKEY | ROOMKEY | CHECKIN | CHECKOUT | ROW ---------------------------------------------------------------- 1 | 8 | 13-4-2010 10:00 | 13-4-2010 11:00 | 1 1 | 1 | 13-4-2010 15:00 | 13-4-2010 16:00 | 1 1 | 1 | 13-4-2010 14:00 | 13-4-2010 15:00 | 2 1 | 1 | 13-4-2010 13:00 | 13-4-2010 14:00 | 3 13 | 2 | 13-4-2010 15:00 | 13-4-2010 16:00 | 1 I want to avoid using cursors so I thought I would use a recursive CTE. Here is what I came up with: ;with CTE (PERSONKEY, ROOMKEY, CHECKIN, CHECKOUT, ROW) as (select RU.PERSONKEY, RU.ROOMKEY, RU.CHECKIN, RU.CHECKOUT, RU.ROW from ROOMUSAGE RU where RU.ROW = 1 union all select RU.PERSONKEY, RU.ROOMKEY, RU.CHECKIN, RU.CHECKOUT, RU.ROW from ROOMUSAGE RU inner join CTE on RU.ROWNUM = CTE.ROWNUM + 1 where CTE.CHECKIN = RU.CHECKOUT and CTE.PERSONKEY = RU.PERSONKEY and CTE.ROOMKEY = RU.ROOMKEY) This worked OK for very small datasets (under 100 records) but it's unusable on large datasets. I'm thinking that I should somehow apply the cte recursevely on each PERSONKEY, ROOMKEY grouping on my ROOMUSAGE table but I am not sure how to do that. Any help would be much appreciated, Cheers!

    Read the article

  • IBM Informix Spatial datablade LIneFromText function

    - by swatit
    Hi Everybody, I am using IBM-Informix for my school project as part of "Informix on-campus" ativity conduted by IBM. I am using spatial datablade to store spatial data. My spatial data table looks like, CREATE TABLE xmlTest (row_id integer NOT NULL,pre integer, post integer,parent integer,tagname varchar(40,1),point ST_POINT); Then I inserted the spatial data into the table. Now I am trying to select the 'Points' lying under given 'polygon', where the co-ordinates of the polygon are dynamic i.e co-ordinates will be decided in the 'select' query. My query is like SELECT v2.* FROM xmlTest v1,xmlTest v2 WHERE ST_Contains(ST_Polygon(ST_LineFromText('linestring (0 0, 22000 0,22000 22000,0 22000,0 0)',5)),ST_Point(v1.pre,v1.post,5)) AND v1.tagname like 'n1' AND ST_Contains(ST_Polygon(ST_LineFromText('linestring (0 0,v1.pre 0,v1.pre v1.post,0 v1.post,0 0 )',5)),ST_Point(v2.pre,v2.post,5)) AND v2.tagname like 'n2' however it is giving me error as "(USE31) - Too few points for geometry type in ST_LineFromText.", in the second linefromtext function. I checked the number of parameters for linestring function, but could not find the source of error. I think,linestring function's parameters should be fixed values and not the variable like in this query. Is it right? Then what is the alternate way, where I can specify my polygon co-ordinates dynamically? or is there any mistake in my query? I hope my question is clear. I appreciate your help!

    Read the article

  • How do I set default search conditions with Searchlogic?

    - by Danger Angell
    I've got a search form on this page: http://staging-checkpointtracker.aptanacloud.com/events If you select a State from the dropdown you get zero results because you didn't select one or more Event Division (checkboxes). What I want is to default the checkboxes to "checked" when the page first loads...to display Events in all Divisions...but I want changes made by the user to be reflected when they filter. Here's the index method in my Events controller: def index @search = Event.search(params[:search]) respond_to do |format| format.html # index.html.erb format.xml { render :xml => @events } end end Here's my search form: <% form_for @search do |f| %> <div> <%= f.label :state_is, "State" %> <%= f.select :state_is, ['AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY'], :include_blank => true %> </div> <div> <%= f.check_box :division_like_any, {:name => "search[:division_like_any][]"}, "Sprint", :checked => true %> Sprint (2+ hours)<br/> <%= f.check_box :division_like_any, {:name => "search[:division_like_any][]"}, "Sport" %> Sport (12+ hours)<br/> <%= f.check_box :division_like_any, {:name => "search[:division_like_any][]"}, "Adventure" %> Adventure (18+ hours)<br/> <%= f.check_box :division_like_any, {:name => "search[:division_like_any][]"}, "Expedition" %> Expedition (48+ hours)<br/> </div> <%= f.submit "Find Events" %> <%= link_to 'Clear', '/events' %> <% end %>

    Read the article

  • Which non-clustered index should I use?

    - 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. CREATE TABLE [dbo].[Customers]( [CustomerId] [int] IDENTITY(1,1) NOT NULL, [CustomerName] [varchar](100) NOT NULL, [Deleted] [bit] NOT NULL, [Active] [bit] NOT NULL, CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ( [CustomerId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] This is the query I'll be using to see what execution plan is showing: SELECT CustomerName FROM Customers Well, executing this command with no additional non-clustered index, it leads the execution plan to show me: I/O cost = 3.45646 Operator cost = 4.57715 Now I'm trying to see if it's possible to improve performance, so I've created a non-clustered index for this table: 1) First non-clustered index 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 Executing again the select against Customers table, the execution plan shows me: I/O cost = 2.79942 Operator cost = 3.92001 It seems better. Now I've deleted this just created non-clustered index, in order to create a new one: 2) First non-clustered index CREATE NONCLUSTERED INDEX [IX_CustomerIDIncludeCustomerName] ON [dbo].[Customers] ( [CustomerId] ASC ) INCLUDE ( [CustomerName]) 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 new non-clustered index, I've executed the select statement again and the execution plan shows me the same result: I/O cost = 2.79942 Operator cost = 3.92001 So, which non-clustered index should I use? Why the costs are the same on execution plan for I/O and Operator? Am I doing something wrong or this is expected? thank you

    Read the article

  • Python and MySQLdb

    - by rohanbk
    I have the following query that I'm executing using a Python script (by using the MySQLdb module). conn=MySQLdb.connect (host = "localhost", user = "root",passwd = "<password>",db = "test") cursor = conn.cursor () preamble='set @radius=%s; set @o_lat=%s; set @o_lon=%s; '%(radius,latitude,longitude) query='SELECT *, 6371*1000 * acos(cos(radians(@o_lat)) * cos(radians(lat)) * cos(radians(lon) - radians(@o_lon)) + sin(radians(@o_lat)) * sin(radians(lat))) as distance FROM poi_table HAVING distance < @radius ORDER BY distance ASC LIMIT 0, 50' complete_query=preamble+query results=cursor.execute (complete_query) print results The values of radius, latitude, and longitude are not important, but they are being defined when the script executes. What bothers me is that the snippet of code above returns no results; essentially meaning that the way that the query is being executed is wonky. I executed the SQL query (including the set variables with actual values, and it returned the correct number of results). If I modify the query to just be a simple SELECT FROM query (SELECT * FROM poi_table) it returns results. What is going on here?

    Read the article

  • Linq-to-SQL produces bad SQL?

    - by strager
    I am trying to run a Linq-to-SQL query, but when the query is evaluated, I get the following exception: System.Data.OleDb.OleDbException was unhandled Message=The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. Source=Microsoft JET Database Engine ErrorCode=-2147217900 StackTrace: at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader() at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataQuery`1.System.Linq.IQueryProvider.Execute[S](Expression expression) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source) at xxx.InventoryPopulator`2.Clear(String barcode) in F:\Projects\C#\xxx\xxx\InventoryPopulator.cs:line 38 [..etc..] InnerException: The debugger shows my query is: SELECT [t0].[SupplierID] AS [Id], [t0].[SupplierSKU] AS [Sku], [t0].[LocalSKU] AS [LocalSku], [t0].[ManufacturersBarcode] AS [Barcode], [t0].[QuantityAvailable] FROM [inventorySupplier] AS [t0] WHERE [t0].[ManufacturersBarcode] = @p0 And the Linq query which generates the above is: var items = from item in this.supplierItems where item.Barcode == barcode select item; How do I fix my query?

    Read the article

  • XSL check param length

    - by AdRock
    I need to check if a param has got a value in it and if it has then do this line otherwise do this line. I've got it working whereas I don't get errors but it's not taking the right branch Here is the template which holds the html in the XSL stylesheet <xsl:call-template name="volunteer_role"> <xsl:with-param name="volrole" select="volunteer/roles" /> </xsl:call-template> and here is the template where there is a choice whether to take this brancg or that brach depending if the param is empty <xsl:template name="volunteer_role"> <xsl:param name="volrole" select="'Not Available'" /> <div class="small bold">ROLES:</div> <div class="large"> <xsl:choose> <xsl:when test="string-length($volrole)!=0"> <xsl:value-of select="$volrole" /> </xsl:when> <xsl:otherwise> <xsl:text> </xsl:text> </xsl:otherwise> </xsl:choose> </div> </xsl:template>

    Read the article

  • Why does the Combo.SelectedValue get lost after some time

    - by tzup
    So I have this asp:DropDownList on a page. It renders like this (in IE7): <select name="ctl00$cphFilter$cbLista" onchange="javascript:setTimeout('__doPostBack(\'ctl00$cphFilter$cbLista\',\'\')', 0)" id="ctl00_cphFilter_cbLista" class="agsSelect"> <option selected="selected" value="4350">A</option> <option value="4352">B</option> <option value="4349">C</option> <option value="4348">D</option> And then I have a grid and a button on the same page. When the user clicks the button the selected item of the dropdown is read (well a datasource object reads it) and the grid does a databind after a trip to a DB where it gets some data based on that selected value. This works fine most of the time. However sometimes, the selection in the dropdownlist seems to get lost even though the rendered page says the A is the selected item. The datasource object is defined like this: <asp:ObjectDataSource ID="dsVM" runat="server" EnablePaging="False" SelectMethod="Select" SortParameterName="sort" TypeName="X.Business.Entities.LPVM.BE"> <SelectParameters> <asp:ControlParameter Name="listaId" Type="Int32" ControlID="cphFilter$cbLista" PropertyName="SelectedValue" /> </SelectParameters> </asp:ObjectDataSource> Any ideas why the grid would reload its data with a select parameter that is 0 instead of the selected value of the dropdownlist? EDIT Suppose the dropdownlist is bound, the user selected B and the grid is bound as well and shows the right data. Now, I wait 2 minutes and I click the Refresh button. Surprisingly, at this particular moment the dropdownlist.SelectedValue (which I already know it was 4352 before I clicked because that's how it looks in the rendered page) is actually an empty string. Where did the value go?

    Read the article

  • Quering XElements for children with children attributes.

    - by Arnej65
    Here is the XML outline: <Root> <Thing att="11"> <Child lang="e"> <record></record> <record></record> <record></record> </Child > <Child lang="f"> <record></record> <record></record> <record></record> </Child > </Thing> </Root> I have the following: TextReader reader = new StreamReader(Assembly.GetExecutingAssembly() .GetManifestResourceStream(FileName)); var data = XElement.Load(reader); foreach (XElement single in Data.Elements()) { // english records var EnglishSet = (from e in single.Elements("Child") where e.Attribute("lang").Equals("e") select e.Value).FirstOrDefault(); } But I'm getting back nothing. I want to be able to for Each "Thing" select the "Child" where the attribute "lang" equals a value. I have also tried this, which has not worked. var FrenchSet = single.Elements("Child") .Where(y => y.Attribute("lang").Equals("f")) .Select(x => x.Value).FirstOrDefault();

    Read the article

  • How can CSS be applied to components from Google Closure Library?

    - by Jason
    I'm getting my feet wet with Google's Closure Library. I've created a simple page with a Select Widget, but it clearly needs some styling (element looks like plain text, and in the example below the menu items pop up beneath the button). I'm assuming the library supports styles -- how can I hook into them? Each example page in SVN seems to use its own CSS. Abbreviated example follows: <body> <div id="inputContainer"></div> <script src="closure-library-read-only/closure/goog/base.js"></script> <script> goog.require('goog.dom'); goog.require('goog.ui.Button'); goog.require('goog.ui.MenuItem'); goog.require('goog.ui.Select'); </script> <script> var inputDiv = goog.dom.$("inputContainer"); var testSelect = new goog.ui.Select("Test selector"); testSelect.addItem(new goog.ui.MenuItem("1")); testSelect.addItem(new goog.ui.MenuItem("2")); testSelect.addItem(new goog.ui.MenuItem("3")); var submitButton = new goog.ui.Button("Go"); testSelect.render(inputDiv); submitButton.render(inputDiv); </script> </body>

    Read the article

  • Why don't file type filters work properly with nsIFilePicker on Mac OSX?

    - by Eric Strom
    I am running a chrome app in firefox (started with -app) with the following code to open a filepicker: var nsIFilePicker = Components.interfaces.nsIFilePicker; var fp = Components.classes["@mozilla.org/filepicker;1"] .createInstance(nsIFilePicker); fp.init(window, "Select Files", nsIFilePicker.modeOpenMultiple); fp.appendFilter("video", "*.mov; *.mpg; *.mpeg; *.avi; *.flv; *.m4v; *.mp4"); fp.appendFilter("all", "*.*"); var res = fp.show(); if (res == nsIFilePicker.returnCancel) return; var files = fp.files; var paths = []; while (files.hasMoreElements()) { var arg = files.getNext().QueryInterface( Components.interfaces.nsILocalFile ).path; paths.push(arg); } Everything seems to work fine on Windows, and the file picker itself works on OSX, but the dropdown menu to select between file types only displays in Windows. The first filter (video in this case) is in effect, but the dropdown to select the other type never shows. Is there something extra that is needed to get this working on OSX? I have tried the latest firefox (3.6) and an older one (3.0.13) and both don't show the file type dropdown on OSX.

    Read the article

  • How do I retrieve row ID from an MVCContrib HTML Grid?

    - by ryandreggs
    Hi, I currently have a product view page that contains an MVCContrib HTML Grid with a select link at the beginning of each row. If the select link is clicked, it takes me to a different page. My question is whether it is possible to retrieve the productID from the row that is selected and pass that to the next page. Maybe this is posible to do with a session variable but im not sure. Any help would be greatly appreciated. Thanks in advance. Here is my view code: <% Html.Grid((List<System2__MVC2.Controllers.ProductController.ProductsSet>)ViewData["Products"]).Columns(column => { column.For(c => Html.ActionLink("Select", "Products", "Product")).DoNotEncode(); column.For(c => c.ProductID); column.For(c => c.Name); column.For(c => c.Description); column.For(c => c.Price); }).Render(); %>

    Read the article

  • Can I spead out a long running stored proc accross multiple CPU's?

    - by Russ
    [Also on SuperUser - http://superuser.com/questions/116600/can-i-spead-out-a-long-running-stored-proc-accross-multiple-cpus] I have a stored procedure in SQL server the gets, and decrypts a block of data. ( Credit cards in this case. ) Most of the time, the performance is tolerable, but there are a couple customers where the process is painfully slow, taking literally 1 minute to complete. ( Well, 59377ms to return from SQL Server to be exact, but it can vary by a few hundred ms based on load ) When I watch the process, I see that SQL is only using a single proc to perform the whole process, and typically only proc 0. Is there a way I can change my stored proc so that SQL can multi-thread the process? Is it even feasible to cheat and to break the calls in half, ( top 50%, bottom 50% ), and spread the load, as a gross hack? ( just spit-balling here ) My stored proc: USE [Commerce] GO /****** Object: StoredProcedure [dbo].[GetAllCreditCardsByCustomerId] Script Date: 03/05/2010 11:50:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetAllCreditCardsByCustomerId] @companyId UNIQUEIDENTIFIER, @DecryptionKey NVARCHAR (MAX) AS SET NoCount ON DECLARE @cardId uniqueidentifier DECLARE @tmpdecryptedCardData VarChar(MAX); DECLARE @decryptedCardData VarChar(MAX); DECLARE @tmpTable as Table ( CardId uniqueidentifier, DecryptedCard NVarChar(Max) ) DECLARE creditCards CURSOR FAST_FORWARD READ_ONLY FOR Select cardId from CreditCards where companyId = @companyId and Active=1 order by addedBy desc --2 OPEN creditCards --3 FETCH creditCards INTO @cardId -- prime the cursor WHILE @@Fetch_Status = 0 BEGIN --OPEN creditCards DECLARE creditCardData CURSOR FAST_FORWARD READ_ONLY FOR select convert(nvarchar(max), DecryptByCert(Cert_Id('Oh-Nay-Nay'), EncryptedCard, @DecryptionKey)) FROM CreditCardData where cardid = @cardId order by valueOrder OPEN creditCardData FETCH creditCardData INTO @tmpdecryptedCardData -- prime the cursor WHILE @@Fetch_Status = 0 BEGIN print 'CreditCardData' print @tmpdecryptedCardData set @decryptedCardData = ISNULL(@decryptedCardData, '') + @tmpdecryptedCardData print '@decryptedCardData' print @decryptedCardData; FETCH NEXT FROM creditCardData INTO @tmpdecryptedCardData -- fetch next END CLOSE creditCardData DEALLOCATE creditCardData insert into @tmpTable (CardId, DecryptedCard) values ( @cardId, @decryptedCardData ) set @decryptedCardData = '' FETCH NEXT FROM creditCards INTO @cardId -- fetch next END select CardId, DecryptedCard FROM @tmpTable CLOSE creditCards DEALLOCATE creditCards

    Read the article

  • MySQL MyISAM table performance... painfully, painfully slow

    - by Salman A
    I've got a table structure that can be summarized as follows: pagegroup * pagegroupid * name has 3600 rows page * pageid * pagegroupid * data references pagegroup; has 10000 rows; can have anything between 1-700 rows per pagegroup; the data column is of type mediumtext and the column contains 100k - 200kbytes data per row userdata * userdataid * pageid * column1 * column2 * column9 references page; has about 300,000 rows; can have about 1-50 rows per page The above structure is pretty straight forwad, the problem is that that a join from userdata to page group is terribly, terribly slow even though I have indexed all columns that should be indexed. The time needed to run a query for such a join (userdata inner_join page inner_join pagegroup) exceeds 3 minutes. This is terribly slow considering the fact that I am not selecting the data column at all. Example of the query that takes too long: SELECT userdata.column1, pagegroup.name FROM userdata INNER JOIN page USING( pageid ) INNER JOIN pagegroup USING( pagegroupid ) Please help by explaining why does it take so long and what can i do to make it faster. Edit #1 Explain returns following gibberish: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE userdata ALL pageid 372420 1 SIMPLE page eq_ref PRIMARY,pagegroupid PRIMARY 4 topsecret.userdata.pageid 1 1 SIMPLE pagegroup eq_ref PRIMARY PRIMARY 4 topsecret.page.pagegroupid 1 Edit #2 SELECT u.field2, p.pageid FROM userdata u INNER JOIN page p ON u.pageid = p.pageid; /* 0.07 sec execution, 6.05 sec fecth */ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE u ALL pageid 372420 1 SIMPLE p eq_ref PRIMARY PRIMARY 4 topsecret.u.pageid 1 Using index SELECT p.pageid, g.pagegroupid FROM page p INNER JOIN pagegroup g ON p.pagegroupid = g.pagegroupid; /* 9.37 sec execution, 60.0 sec fetch */ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE g index PRIMARY PRIMARY 4 3646 Using index 1 SIMPLE p ref pagegroupid pagegroupid 5 topsecret.g.pagegroupid 3 Using where Moral of the story Keep medium/long text columns in a separate table if you run into performance problems such as this one.

    Read the article

  • Can I spread out a long running stored proc accross multiple CPU's?

    - by Russ
    [Also on SuperUser - http://superuser.com/questions/116600/can-i-spead-out-a-long-running-stored-proc-accross-multiple-cpus] I have a stored procedure in SQL server the gets, and decrypts a block of data. ( Credit cards in this case. ) Most of the time, the performance is tolerable, but there are a couple customers where the process is painfully slow, taking literally 1 minute to complete. ( Well, 59377ms to return from SQL Server to be exact, but it can vary by a few hundred ms based on load ) When I watch the process, I see that SQL is only using a single proc to perform the whole process, and typically only proc 0. Is there a way I can change my stored proc so that SQL can multi-thread the process? Is it even feasible to cheat and to break the calls in half, ( top 50%, bottom 50% ), and spread the load, as a gross hack? ( just spit-balling here ) My stored proc: USE [Commerce] GO /****** Object: StoredProcedure [dbo].[GetAllCreditCardsByCustomerId] Script Date: 03/05/2010 11:50:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetAllCreditCardsByCustomerId] @companyId UNIQUEIDENTIFIER, @DecryptionKey NVARCHAR (MAX) AS SET NoCount ON DECLARE @cardId uniqueidentifier DECLARE @tmpdecryptedCardData VarChar(MAX); DECLARE @decryptedCardData VarChar(MAX); DECLARE @tmpTable as Table ( CardId uniqueidentifier, DecryptedCard NVarChar(Max) ) DECLARE creditCards CURSOR FAST_FORWARD READ_ONLY FOR Select cardId from CreditCards where companyId = @companyId and Active=1 order by addedBy desc --2 OPEN creditCards --3 FETCH creditCards INTO @cardId -- prime the cursor WHILE @@Fetch_Status = 0 BEGIN --OPEN creditCards DECLARE creditCardData CURSOR FAST_FORWARD READ_ONLY FOR select convert(nvarchar(max), DecryptByCert(Cert_Id('Oh-Nay-Nay'), EncryptedCard, @DecryptionKey)) FROM CreditCardData where cardid = @cardId order by valueOrder OPEN creditCardData FETCH creditCardData INTO @tmpdecryptedCardData -- prime the cursor WHILE @@Fetch_Status = 0 BEGIN print 'CreditCardData' print @tmpdecryptedCardData set @decryptedCardData = ISNULL(@decryptedCardData, '') + @tmpdecryptedCardData print '@decryptedCardData' print @decryptedCardData; FETCH NEXT FROM creditCardData INTO @tmpdecryptedCardData -- fetch next END CLOSE creditCardData DEALLOCATE creditCardData insert into @tmpTable (CardId, DecryptedCard) values ( @cardId, @decryptedCardData ) set @decryptedCardData = '' FETCH NEXT FROM creditCards INTO @cardId -- fetch next END select CardId, DecryptedCard FROM @tmpTable CLOSE creditCards DEALLOCATE creditCards

    Read the article

  • Can I spread out a long running stored proc accross multiple CPU's?

    - by Russ
    [Also on SuperUser - http://superuser.com/questions/116600/can-i-spead-out-a-long-running-stored-proc-accross-multiple-cpus] I have a stored procedure in SQL server the gets, and decrypts a block of data. ( Credit cards in this case. ) Most of the time, the performance is tolerable, but there are a couple customers where the process is painfully slow, taking literally 1 minute to complete. ( Well, 59377ms to return from SQL Server to be exact, but it can vary by a few hundred ms based on load ) When I watch the process, I see that SQL is only using a single proc to perform the whole process, and typically only proc 0. Is there a way I can change my stored proc so that SQL can multi-thread the process? Is it even feasible to cheat and to break the calls in half, ( top 50%, bottom 50% ), and spread the load, as a gross hack? ( just spit-balling here ) My stored proc: USE [Commerce] GO /****** Object: StoredProcedure [dbo].[GetAllCreditCardsByCustomerId] Script Date: 03/05/2010 11:50:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetAllCreditCardsByCustomerId] @companyId UNIQUEIDENTIFIER, @DecryptionKey NVARCHAR (MAX) AS SET NoCount ON DECLARE @cardId uniqueidentifier DECLARE @tmpdecryptedCardData VarChar(MAX); DECLARE @decryptedCardData VarChar(MAX); DECLARE @tmpTable as Table ( CardId uniqueidentifier, DecryptedCard NVarChar(Max) ) DECLARE creditCards CURSOR FAST_FORWARD READ_ONLY FOR Select cardId from CreditCards where companyId = @companyId and Active=1 order by addedBy desc --2 OPEN creditCards --3 FETCH creditCards INTO @cardId -- prime the cursor WHILE @@Fetch_Status = 0 BEGIN --OPEN creditCards DECLARE creditCardData CURSOR FAST_FORWARD READ_ONLY FOR select convert(nvarchar(max), DecryptByCert(Cert_Id('Oh-Nay-Nay'), EncryptedCard, @DecryptionKey)) FROM CreditCardData where cardid = @cardId order by valueOrder OPEN creditCardData FETCH creditCardData INTO @tmpdecryptedCardData -- prime the cursor WHILE @@Fetch_Status = 0 BEGIN print 'CreditCardData' print @tmpdecryptedCardData set @decryptedCardData = ISNULL(@decryptedCardData, '') + @tmpdecryptedCardData print '@decryptedCardData' print @decryptedCardData; FETCH NEXT FROM creditCardData INTO @tmpdecryptedCardData -- fetch next END CLOSE creditCardData DEALLOCATE creditCardData insert into @tmpTable (CardId, DecryptedCard) values ( @cardId, @decryptedCardData ) set @decryptedCardData = '' FETCH NEXT FROM creditCards INTO @cardId -- fetch next END select CardId, DecryptedCard FROM @tmpTable CLOSE creditCards DEALLOCATE creditCards

    Read the article

  • Hi how to show the results in a datatable while we are using yui

    - by udaya
    Hi I am using yui to display a datagrid ... <?php $host = "localhost"; //database location $user = "root"; //database username $pass = ""; //database password $db_name = "cms"; //database name //database connection $link = mysql_connect($host, $user, $pass); mysql_select_db($db_name); //sets encoding to utf8 $result = mysql_query("select dStud_id,dMarkObtained1,dMarkObtained2,dMarkObtained3,dMarkTotal from tbl_internalmarkallot"); //$res = mysql_fetch_array($result); while($res = mysql_fetch_array($result)) { //print_r($res); $JsonVar = json_encode($res); echo "<input type='text' name='json' id='json' value ='$JsonVar'>"; } //print_r (mysql_fetch_array($result)); //echo "<input type='text' name='json' id='json' value ='$JsonVar'>"; ?> <html> <head> <meta http-equiv="content-type" content="text/html; charset=utf-8"> <title>Client-side Pagination</title> <style type="text/css"> body { margin:0; padding:0; } </style> <link rel="stylesheet" type="text/css" href="build/fonts/fonts-min.css" /> <link rel="stylesheet" type="text/css" href="build/paginator/assets/skins/sam/paginator.css" /> <link rel="stylesheet" type="text/css" href="build/datatable/assets/skins/sam/datatable.css" /> <script type="text/javascript" src="build/yahoo-dom-event/yahoo-dom-event.js"></script> <script type="text/javascript" src="build/connection/connection-min.js"></script> <script type="text/javascript" src="build/json/json-min.js"></script> <script type="text/javascript" src="build/element/element-min.js"></script> <script type="text/javascript" src="build/paginator/paginator-min.js"></script> <script type="text/javascript" src="build/datasource/datasource-min.js"></script> <script type="text/javascript" src="build/datatable/datatable-min.js"></script> <script type="text/javascript" src="YuiJs.js"></script> <style type="text/css"> #paginated { text-align: center; } #paginated table { margin-left:auto; margin-right:auto; } #paginated, #paginated .yui-dt-loading { text-align: center; background-color: transparent; } </style> </head> <body class="yui-skin-sam" onload="ProjectDatatable(document.getElementById('json').value);"> <h1>Client-side Pagination</h1> <div class="exampleIntro"> </div> <input type="hidden" id="HfId"/> <div id="paginated"> </div> <script type="text/javascript"> /*YAHOO.util.Event.onDOMReady(function() { YAHOO.example.ClientPagination = function() { var myColumnDefs = [ {key:"dStud_id", label:"ID",sortable:true, resizeable:true, editor: new YAHOO.widget.TextareaCellEditor()}, {key:"dMarkObtained1", label:"Name",sortable:true}, {key:"dMarkObtained2", label:"CycleTest1"}, {key:"dMarkObtained3", label:"CycleTest2"}, {key:"dMarkTotal", label:"CycleTest3"}, ]; var myDataSource = new YAHOO.util.DataSource("assets/php/json_proxy.php?"); myDataSource.responseType = YAHOO.util.DataSource.TYPE_JSON; myDataSource.responseSchema = { resultsList: "records", fields: ["dStud_id","dMarkObtained1","dMarkObtained2","dMarkObtained3","dMarkTotal"] }; var oConfigs = { paginator: new YAHOO.widget.Paginator({ rowsPerPage: 15 }), initialRequest: "results=504" }; var myDataTable = new YAHOO.widget.DataTable("paginated", myColumnDefs, myDataSource, oConfigs); return { oDS: myDataSource, oDT: myDataTable }; }(); });*/ </script> <?php echo "m".$res['dMarkObtained1']; echo "m".$res['dMarkObtained2']; echo "m".$res['dMarkObtained3']; echo "Tm".$res['dMarkTotal']; {?><? }?> </body> </html> </body> </html> This is my page where i am fetching the data's from the database function generateDatatable(target, jsonObj, myColumnDefs, hfId) { var root; for (key in jsonObj) { root = key; break; } var rootId = "id"; if (jsonObj[root].length > 0) { for (key in jsonObj[root][0]) { rootId = key; break; } } YAHOO.example.DynamicData = function() { var myPaginator = new YAHOO.widget.Paginator({ rowsPerPage: 10, template: YAHOO.widget.Paginator.TEMPLATE_ROWS_PER_PAGE, rowsPerPageOptions: [5, 25, 50, 100], pageLinks: 10 }); // DataSource instance var myDataSource = new YAHOO.util.DataSource(jsonObj); myDataSource.responseType = YAHOO.util.DataSource.TYPE_JSON; myDataSource.responseSchema = { resultsList: root, fields: new Array() }; myDataSource.responseSchema.fields[0] = rootId; for (var i = 0; i < myColumnDefs.length; i++) { myDataSource.responseSchema.fields[i + 1] = myColumnDefs[i].key; } // DataTable configuration var myConfigs = { sortedBy: { key: myDataSource.responseSchema.fields[1], dir: YAHOO.widget.DataTable.CLASS_ASC }, // Sets UI initial sort arrow paginator: myPaginator }; // DataTable instance var myDataTable = new YAHOO.widget.DataTable(target, myColumnDefs, myDataSource, myConfigs); myDataTable.subscribe("rowMouseoverEvent", myDataTable.onEventHighlightRow); myDataTable.subscribe("rowMouseoutEvent", myDataTable.onEventUnhighlightRow); myDataTable.subscribe("rowClickEvent", myDataTable.onEventSelectRow); myDataTable.subscribe("checkboxClickEvent", function(oArgs) { var hidObj = document.getElementById(hfId); var elCheckbox = oArgs.target; var oRecord = this.getRecord(elCheckbox); var id = oRecord.getData(rootId); if (elCheckbox.checked) { if (hidObj.value == "") { hidObj.value = id; } else { hidObj.value += "," + id; } } else { hidObj.value = removeIdFromArray("" + hfId, id); } }); myPaginator.subscribe("changeRequest", function() { if (document.getElementById(hfId).value != "") { /*if (document.getElementById("ConfirmationPanel").style.display == 'block') { document.getElementById("ConfirmationPanel").style.display = 'none'; }*/ document.getElementById(hfId).value = ""; } return true; }); myDataTable.handleDataReturnPayload = function(oRequest, oResponse, oPayload) { oPayload.totalRecords = oResponse.meta.totalRecords; return oPayload; } return { ds: myDataSource, dt: myDataTable }; } (); } function removeIdFromArray(values, id) { values = document.getElementById(values).value; if (values.indexOf(',') == 0) { values = values.substring(1); } if (values.indexOf(values.length - 1) == ",") { values = values.substring(0, values.length - 1); } var ids = values.split(','); var rtnValue = ""; for (var i = 0; i < ids.length; i++) { if (ids[i] != id) { rtnValue += "," + ids[i]; } } if (rtnValue.indexOf(",") == 0) { rtnValue = rtnValue.substring(1); } return rtnValue; } function edityuitable() { var ErrorDiv = document.getElementById("ErrorDiv"); var editId=document.getElementById("ctl00_ContentPlaceHolder1_HfId").value; if(editId.length == 0) { ErrorDiv.innerHTML = getErrorMsgStyle("Select a row for edit"); //alert("Select a row for edit"); return false; } else { var editarray = editId.split(","); if (editarray.length != 1) { ErrorDiv.innerHTML = getErrorMsgStyle("Select One row for edit"); //alert("Select One row for edit"); return false; } else if (editarray.length == 1) { return true; } } } function Deleteyuitable() { var ErrorDiv = document.getElementById("ErrorDiv"); var editId=document.getElementById("ctl00_ContentPlaceHolder1_HfId").value; if(editId.length == 0) { ErrorDiv.innerHTML = getErrorMsgStyle("Select a row for Delete"); return false; } else { return true; } } function ProjectDatatable(HfJsonValue){ alert(HfJsonValue); var myColumnDefs = [ {key:"dStud_id", label:"ID", width:150, sortable:true, sortOptions:{defaultDir:YAHOO.widget.DataTable.CLASS_DESC}}, {key:"dMarkObtained1", label:"Marks", width:200, sortable:true, sortOptions:{defaultDir:YAHOO.widget.DataTable.CLASS_DESC}}, {key:"dMarkObtained2", label:"Marks1", width:150, sortable:true, sortOptions:{defaultDir:YAHOO.widget.DataTable.CLASS_DESC}}, {key:"dMarkObtained3", label:"Marks2", width:200, sortable:true, sortOptions:{defaultDir:YAHOO.widget.DataTable.CLASS_DESC}}, {key:"dMarkTotal", label:"Total", width:150, sortable:true, sortOptions:{defaultDir:YAHOO.widget.DataTable.CLASS_DESC}}, {key:"", formatter:"checkbox"} ]; var jsonObj=eval('(' + HfJsonValue + ')'); var target = "paginated"; var hfId = "HfId"; generateDatatable(target,jsonObj,myColumnDefs,hfId) } // JavaScript Document This is my script page when i load the page i do get the first row from the database but the consequtive data's are not displayed in the alert box how can i receieve the data's in the datagrid

    Read the article

  • ASP.NET RadioButton messing with the name (groupname)

    - by Hojou
    I got a templated control (a repeater) listing some text and other markup. Each item has a radiobutton associated with it, making it possible for the user to select ONE of the items created by the repeater. The repeater writes the radiobutton setting its id and name generated with the default asp.net naming convention making each radiobutton a full 'group'. This means all radiobuttons are independant on each other, which again unfortunately means i can select all radiobuttons at the same time. The radiobutton has the clever attribute 'groupname' used to set a common name so they get grouped together and thus should be dependant (so i can only select one at a time). The problem is - this doesn't work - the repeater makes sure the id and thus the name (which controls the grouping) are different. Since i use a repeater (could have been a listview or any other templated databound control) i can't use the RadioButtonList. So where does that leave me? I know i've had this problem before and solved it. I know almost every asp.net programmer must have had it too, so why can't i google and find a solid solution to the problem? I came across solutions to enforce the grouping by javascript (ugly!) or even to handle the radiobuttons as non-server controls, forcing me to do a Request.Form[name] to read the status. I also tried experimenting with overriding the name attribute on the PreRender event - unfortunately the owning page and masterpage again overrides this name to reflect the full id/name so i end up with the same wrong result. If you have no better solution than i posted, you are still very welcome to post your thoughts - atleast i'll know that my friend 'jack' is right about how messed up 'asp.net' is sometimes ;)

    Read the article

  • How to call Postgres function returning SETOF record?

    - by Peter
    I have written the following function: -- Gets stats for all markets CREATE OR REPLACE FUNCTION GetMarketStats ( ) RETURNS SETOF record AS $$ BEGIN SELECT 'R approved offer' AS Metric, SUM(CASE WHEN M.MarketName = 'A+' AND M.Term = 24 THEN LO.Amount ELSE 0 end) AS MarketAPlus24, SUM(CASE WHEN M.MarketName = 'A+' AND M.Term = 36 THEN LO.Amount ELSE 0 end) AS MarketAPlus36, SUM(CASE WHEN M.MarketName = 'A' AND M.Term = 24 THEN LO.Amount ELSE 0 end) AS MarketA24, SUM(CASE WHEN M.MarketName = 'A' AND M.Term = 36 THEN LO.Amount ELSE 0 end) AS MarketA36, SUM(CASE WHEN M.MarketName = 'B' AND M.Term = 24 THEN LO.Amount ELSE 0 end) AS MarketB24, SUM(CASE WHEN M.MarketName = 'B' AND M.Term = 36 THEN LO.Amount ELSE 0 end) AS MarketB36 FROM "Market" M INNER JOIN "Listing" L ON L.MarketID = M.MarketID INNER JOIN "ListingOffer" LO ON L.ListingID = LO.ListingID; END $$ LANGUAGE plpgsql; And when trying to call it like this... select * from GetMarketStats() AS (Metric VARCHAR(50),MarketAPlus24 INT,MarketAPlus36 INT,MarketA24 INT,MarketA36 INT,MarketB24 INT,MarketB36 INT); I get an error: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function "getmarketstats" line 2 at SQL statement I don't understand this output. I've tried using perform too, but I thought one only had to use that if the function doesn't return anything.

    Read the article

  • Why does a conditional not affect query speed?

    - by Telos
    I have a stored procedure that was taking a "long" period of time to execute. The query only needs to return data in one case, so I figured I could check for that case and just return before hitting the actual query. The only problem is that it still takes the same amount of time to execute with an if statement. I have verified that the code inside the if is not executing, and that if I replace the complex query with a simple select the speed is fine... so now I'm confused. Why is the query being slowed down by code that doesn't get executed when the conditional is false? Here's the query itself: ALTER PROCEDURE [dbo].[pr_cbc_GetCokeInfo] @pa_record int, @pb_record int AS BEGIN SET NOCOUNT ON; declare @ticketRec int SELECT @ticketRec = TicketRecord FROM eservice_live..v_sdticket where TicketRecord=@pa_record AND serviceCompanyID = 1139 AND @pb_record IS NULL if @ticketRec IS NULL return select record = null, doc_ref = @pa_record, memo_type = 'I', memo = 'Bottler: ' + isnull(Bottler, '') + ' ' + 'Sales Loc: ' + isnull(SalesLocation, '') + ' ' + 'Outlet Desc: ' + isnull(OutletDesc, '') + ' ' + 'City: ' + isnull(OutletCity, '') + ' ' + 'EquipNo: ' + isnull(EquipNo, '') + ' ' + 'SerialNo: ' + isnull(SerialNo, '') + ' ' + 'PhaseNo: ' + isnull(cast(PhaseNo as varchar(255)), '') + ' ' + 'StaticIP: ' + isnull(StaticIP, '') + ' ' + 'Air Card: ' + isnull(AirCard, '') FROM eservice_live..v_SDExtendedInfoField ef JOIN eservice_live..CokeSNList csl ON ef.valueText=csl.SerialNo where ef.docType='CLH' AND ef.docref = @ticketRec AND ef.ExtendedDocNumber=5 SET NOCOUNT OFF; END

    Read the article

  • I am using relational division with EAV, but I need to find results in EAV that have some of the cat

    - by NewToDB
    I have two tables: CREATE TABLE EAV ( subscriber_id INT(1) NOT NULL DEFAULT '0', attribute_id CHAR(62) NOT NULL DEFAULT '', attribute_value CHAR(62) NOT NULL DEFAULT '', PRIMARY KEY (subscriber_id,attribute_id) ) INSERT INTO EAV (subscriber_id, attribute_id, attribute_value) VALUES (1,'color','red') INSERT INTO EAV (subscriber_id, attribute_id, attribute_value) VALUES (1,'size','xl') INSERT INTO EAV (subscriber_id, attribute_id, attribute_value) VALUES (1,'garment','shirt') INSERT INTO EAV (subscriber_id, attribute_id, attribute_value) VALUES (2,'color','red') INSERT INTO EAV (subscriber_id, attribute_id, attribute_value) VALUES (2,'size','xl') INSERT INTO EAV (subscriber_id, attribute_id, attribute_value) VALUES (2,'garment','pants') INSERT INTO EAV (subscriber_id, attribute_id, attribute_value) VALUES (3,'garment','pants') CREATE TABLE CRITERIA ( attribute_id CHAR(62) NOT NULL DEFAULT '', attribute_value CHAR(62) NOT NULL DEFAULT '' ) INSERT INTO CRITERIA (attribute_id, attribute_value) VALUES ('color', 'red') INSERT INTO CRITERIA (attribute_id, attribute_value) VALUES ('size', 'xl') To find all subscribers in the EAV that match my criteria, I use relational division: SELECT DISTINCT(subscriber_id) FROM EAV WHERE subscriber_id IN (SELECT E.subscriber_id FROM EAV AS E JOIN CRITERIA AS CR ON E.attribute_id = CR.attribute_id AND E.attribute_value = CR.attribute_value GROUP BY E.subscriber_id HAVING COUNT() = (SELECT COUNT() FROM CRITERIA)) This gives me an unique list of subscribers who have all the criteria. So that means I get back subscriber 1 and 2 since they are looking for the color red and size xl, and that's exactly my criteria. But what if I want to extend this so that I also get subscriber 3 since this subscriber didn't specifically say what color or size they want (ie. there is no entry for attribute 'color' or 'size' in the EAV table for subscriber 3). Given my current design, is there a way I can extend my query to include subscribers that have zero or more of the attributes defined, and if they do have the attribute defined, then it must match the criteria? Or is there a better way to design the table to aid in querying?

    Read the article

  • strategy for choosing proper object and proper method

    - by zerkms
    in the code below at first if statements block (there will be more than just "worker" condition, joined with else if) i select proper filter_object. After this in the same conditional block i select what filter should be applied by filter object. This code is silly. public class Filter { public static List<data.Issue> fetch(string type, string filter) { Filter_Base filter_object = new Filter_Base(filter); if (type == "worker") { filter_object = new Filter_Worker(filter); } else if (type == "dispatcher") { filter_object = new Filter_Dispatcher(filter); } List<data.Issue> result = new List<data.Issue>(); if (filter == "new") { result = filter_object.new_issues(); } else if (filter == "ended") { result = filter_object.ended_issues(); } return result; } } public class Filter_Base { protected string _filter; public Filter_Base(string filter) { _filter = filter; } public virtual List<data.Issue> new_issues() { return new List<data.Issue>(); } public virtual List<data.Issue> ended_issues() { return new List<data.Issue>(); } } public class Filter_Worker : Filter_Base { public Filter_Worker(string filter) : base(filter) { } public override List<data.Issue> new_issues() { return (from i in data.db.GetInstance().Issues where (new int[] { 4, 5 }).Contains(i.RequestStatusId) select i).Take(10).ToList(); } } public class Filter_Dispatcher : Filter_Base { public Filter_Dispatcher(string filter) : base(filter) { } } it will be used in some kind of: Filter.fetch("worker", "new"); this code means, that for user that belongs to role "worker" only "new" issues will be fetched (this is some kind of small and simple CRM). Or another: Filter.fetch("dispatcher", "ended"); // here we get finished issues for dispatcher role Any proposals on how to improve it?

    Read the article

  • How can I extend this SQL query to find the k nearest neighbors?

    - by Smigs
    I have a database full of two-dimensional data - points on a map. Each record has a field of the geometry type. What I need to be able to do is pass a point to a stored procedure which returns the k nearest points (k would also be passed to the sproc, but that's easy). I've found a query at http://blogs.msdn.com/isaac/archive/2008/10/23/nearest-neighbors.aspx which gets the single nearest neighbour, but I can't figure how to extend it to find the k nearest neighbours. This is the current query - T is the table, g is the geometry field, @x is the point to search around, Numbers is a table with integers 1 to n: DECLARE @start FLOAT = 1000; WITH NearestPoints AS ( SELECT TOP(1) WITH TIES *, T.g.STDistance(@x) AS dist FROM Numbers JOIN T WITH(INDEX(spatial_index)) ON T.g.STDistance(@x) < @start*POWER(2,Numbers.n) ORDER BY n ) SELECT TOP(1) * FROM NearestPoints ORDER BY n, dist The inner query selects the nearest non-empty region and the outer query then selects the top result from that region; the outer query can easily be changed to (e.g.) SELECT TOP(20), but if the nearest region only contains one result, you're stuck with that. I figure I probably need to recursively search for the first region containing k records, but without using a table variable (which would cause maintenance problems as you have to create the table structure and it's liable to change - there're lots of fields), I can't see how.

    Read the article

  • How can I work around SQL Server - Inline Table Value Function execution plan variation based on par

    - by Ovidiu Pacurar
    Here is the situation: I have a table value function with a datetime parameter ,lest's say tdf(p_date) , that filters about two million rows selecting those with column date smaller than p_date and computes some aggregate values on other columns. It works great but if p_date is a custom scalar value function (returning the end of day in my case) the execution plan is altered an the query goes from 1 sec to 1 minute execution time. A proof of concept table - 1K products, 2M rows: CREATE TABLE [dbo].[POC]( [Date] [datetime] NOT NULL, [idProduct] [int] NOT NULL, [Quantity] [int] NOT NULL ) ON [PRIMARY] The inline table value function: CREATE FUNCTION tdf (@p_date datetime) RETURNS TABLE AS RETURN ( SELECT idProduct, SUM(Quantity) AS TotalQuantity, max(Date) as LastDate FROM POC WHERE (Date < @p_date) GROUP BY idProduct ) The scalar value function: CREATE FUNCTION [dbo].[EndOfDay] (@date datetime) RETURNS datetime AS BEGIN DECLARE @res datetime SET @res=dateadd(second, -1, dateadd(day, 1, dateadd(ms, -datepart(ms, @date), dateadd(ss, -datepart(ss, @date), dateadd(mi,- datepart(mi,@date), dateadd(hh, -datepart(hh, @date), @date)))))) RETURN @res END Query 1 - Working great SELECT * FROM [dbo].[tdf] (getdate()) The end of execution plan: Stream Aggregate Cost 13% <--- Clustered Index Scan Cost 86% Query 2 - Not so great SELECT * FROM [dbo].[tdf] (dbo.EndOfDay(getdate())) The end of execution plan: Stream Aggregate Cost 4% <--- Filter Cost 12% <--- Clustered Index Scan Cost 86%

    Read the article

< Previous Page | 330 331 332 333 334 335 336 337 338 339 340 341  | Next Page >