Search Results

Search found 10101 results on 405 pages for 'temporary tables'.

Page 17/405 | < Previous Page | 13 14 15 16 17 18 19 20 21 22 23 24  | Next Page >

  • 100+ tables to joined

    - by deian
    Hi guys, I was wondering if anyone ever had a change to measure how a would 100 joined tables perform? Each table would have an ID column with primary index and all table are 1:1 related. It is a common problem within many data entry applications where we need to collect 1000+ data points. One solution would be to have one big table with 1000+ columns and the alternative would be to split them into multiple tables and join them when it is necessary. So perhaps more real question would be how 30 tables (30 columns each) would behave with multitable join. 500K-1M rows should be the expected size of the tables. Cheers

    Read the article

  • Programmatically creating linked tables in access

    - by newtoaccess
    Hi all, We need to find a way to programatically *link all the tables* in a SQL Server database to an access db. We will be invoking this access database from a program that uses .net/SQL Server 2008. While invoking the application we would like to add the linked tables so that the users can just run the reports/modules from access without having to worry about linking the tables. Is there a way we can do this? Thanks!

    Read the article

  • Accessing inter-schema tables and relations in hibernate

    - by nitesh
    There is a typical situation being faced where different tables are scattered through different schemas in Oracle database and they are related to each other (encompassing all different types of relations). How can they be represented in Hibernate using annotations as when a sessionfactory handle is created for one schema, tables in that schema can't access other related tables (foreign key relation to tables in other schema)? For a query like following, exception is thrown - "from table1 as model where model.table2Name.table2column = "+foo Exception comes as - org.hibernate.QueryException: could not resolve property: table2column of: com.test.table1 [from com.test.table1 as model where model.table2Name.table2column = 1] Here table1 and table2 are present in different schemas.

    Read the article

  • Large number of tables and Hibernate memory consumption

    - by Vedran
    I'm working on a large ERP project which has database model with about 2100 tables. With "only" 500 tables mapped with Hibernate, application deployed on the web server takes about 3GB of working memory. Is there any way to reduce Hibernate's metamodel memory footprint when using that many tables in one persistence unit? Or should I just give up on ORMs and go with plain old JDBC (or even jOOQ)? Right now I'm using Hibernate 4.1.8, Spring 3.1.3, JBoss AS 7.1 and working with MSSQL database. Edit: JavaMelody memory histogram output - with 2000 generated test tables that are a bit smaller in scope from the original db model (hence 'only' 1.3GB of spent memory)

    Read the article

  • Updating data source on multiple pivot tables within Excel

    - by phrenetic
    Is there an easy way to update the data source for multiple pivot tables on a single Excel sheet at the same time? All of the pivot tables reference the same named range, but I need to create a second worksheet that has the same pivot tables, but accessing a different named range. Ideally I would like to be able to do some kind of search and replace operation (like you can do on formulae), rather than updating each individual pivot table by hand. Any suggestions?

    Read the article

  • best way to create tables with ORM?

    - by ajsie
    assume that i start coding an application from scratch, is the best way to create tables when using an ORM (doctrine), to manually create tables in mysql and then generate models from the tables, or is it the other way around, that is to create the models in php and then generate tables from models? and if i already have a database, will the models created be optimal? cause i have heard some say that its best to create the database from scratch when using ORM, so that the relations are optimized for OOD. share your thoughts!

    Read the article

  • Common Properties: Consolidating Loan, Purchase, Inventory and Sale tables into one Transaction tabl

    - by Frank Computer
    Pawnshop Application: I have separate tables for Loan, Purchase, Inventory & Sales transactions. Each tables rows are joined to their respective customer rows by: customer.pk [serial] = loan.fk [integer]; = purchase.fk [integer]; = inventory.fk [integer]; = sale.fk [integer]; Since there are so many common properties within the four tables, I consolidated the four tables into one table called "transaction", where a column: transaction.trx_type char(1) {L=Loan, P=Purchase, I=Inventory, S=Sale} Scenario: A customer initially pawns merchandise, makes a couple of interest payments, then decides he wants to sell the merchandise to the pawnshop, who then places merchandise in Inventory and eventually sells it to another customer. I designed a generic transaction table where for example: transaction.main_amount DECIMAL(7,2) in a loan transaction holds the pawn amount, in a purchase holds the purchase price, in inventory and sale holds sale price. This is clearly a denormalized design, but has made programming alot easier and improved performance. Any type of transaction can now be performed from within one screen, without the need to change to different tables.

    Read the article

  • Select value from database and store into a temporary variable

    - by user1616230
    I want to select a stored value from database and then put it into a temporary variable. For example, I have a column called category, one value under it is m, so I want to select this m value from the database, let's say from a table of a database called user_info. Then I want to put it into a variable, let's name it $res. After that, I want to do some condition stuff, such as if $res=="m", Can anyone help me write a simple structure here? Here is the code: <?php $sql = "Select category FROM user_info WHERE user_name = '" .$_SESSION['username']."' and password = '".$_SESSION['password']."'"; $res = mysql_query($sql); if($res == "a"){ include('MPIncomeStrategy.php'); } if($res == "b"){ include('MPIncomeStrategy.php'); } But it seems that the code is not able to detect $res =="category value in database". Did I just use the wrong way to store the category value?

    Read the article

  • How atomic is a SELECT INTO?

    - by leo.pasta
    Last week I got an interesting situation that prompted me to challenge a long standing assumption. I always thought that a SELECT INTO was an atomic statement, i.e. it would either complete successfully or the table would not be created. So I got very surprised when, after a “select into” query was chosen as a deadlock victim, the next execution (as the app would handle the deadlock and retry) would fail with: Msg 2714, Level 16, State 6, Line 1 There is already an object named '#test' in the database. The only hypothesis we could come up was that the “create table” part of the statement was committed independently from the actual “insert”. We can confirm that by capturing the “Transaction Log” event on Profiler (filtering by SPID0). The result is that when we run: SELECT * INTO #results FROM master.sys.objects we get the following output on Profiler: It is easy to see the two independent transactions. Although this behaviour was a surprise to me, it is very easy to workaround it if you feel the need (as we did in this case). You can either change it into independent “CREATE TABLE / INSERT SELECT” or you can enclose the SELECT INTO in an explicit transaction: SET XACT_ABORT ON BEGIN TRANSACTION SELECT * INTO #results FROM master.sys.objects COMMIT

    Read the article

  • Unable to generate a temporary class (result=1).\r\nerror CS0030:- c#

    - by ltech
    Running XSD.exe on my xml to generate C# class. All works well except on this property public DocumentATTRIBUTES[][] Document { get { return this.documentField; } set { this.documentField = value; } } I want to try and use CollectionBase, and this was my attempt public DocumentATTRIBUTESCollection Document { get { return this.documentField; } set { this.documentField = value; } } /// <remarks/> [System.SerializableAttribute()] [System.Diagnostics.DebuggerStepThroughAttribute()] [System.ComponentModel.DesignerCategoryAttribute("code")] [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)] public partial class DocumentATTRIBUTES { private string _author; private string _maxVersions; private string _summary; /// <remarks/> [System.Xml.Serialization.XmlElementAttribute(Form = System.Xml.Schema.XmlSchemaForm.Unqualified)] public string author { get { return _author; } set { _author = value; } } /// <remarks/> [System.Xml.Serialization.XmlElementAttribute(Form = System.Xml.Schema.XmlSchemaForm.Unqualified)] public string max_versions { get { return _maxVersions; } set { _maxVersions = value; } } /// <remarks/> [System.Xml.Serialization.XmlElementAttribute(Form = System.Xml.Schema.XmlSchemaForm.Unqualified)] public string summary { get { return _summary; } set { _summary = value; } } } public class DocumentAttributeCollection : System.Collections.CollectionBase { public DocumentAttributeCollection() : base() { } public DocumentATTRIBUTES this[int index] { get { return (DocumentATTRIBUTES)this.InnerList[index]; } } public void Insert(int index, DocumentATTRIBUTES value) { this.InnerList.Insert(index, value); } public int Add(DocumentATTRIBUTES value) { return (this.InnerList.Add(value)); } } However when I try to serialize my object using XmlSerializer serializer = new XmlSerializer(typeof(DocumentMetaData)); I get the error: {"Unable to generate a temporary class (result=1).\r\nerror CS0030: Cannot convert type 'DocumentATTRIBUTES' to 'DocumentAttributeCollection'\r\nerror CS1502: The best overloaded method match for 'DocumentAttributeCollection.Add(DocumentATTRIBUTES)' has some invalid arguments\r\nerror CS1503: Argument '1': cannot convert from 'DocumentAttributeCollection' to 'DocumentATTRIBUTES'\r\n"}

    Read the article

  • Temporary non-const istream reference in constructor (C++)

    - by Christopher Bruns
    It seems that a constructor that takes a non-const reference to an istream cannot be constructed with a temporary value in C++. #include <iostream> #include <sstream> using namespace std; class Bar { public: explicit Bar(std::istream& is) {} }; int main() { istringstream stream1("bar1"); Bar bar1(stream1); // OK on all platforms // compile error on linux, Mac gcc; OK on Windows MSVC Bar bar2(istringstream("bar2")); return 0; } This compiles fine with MSVC, but not with gcc. Using gcc I get a compile error: g++ test.cpp -o test test.cpp: In function ‘int main()’: test.cpp:18: error: no matching function for call to ‘Bar::Bar(std::istringstream)’ test.cpp:9: note: candidates are: Bar::Bar(std::istream&) test.cpp:7: note: Bar::Bar(const Bar&) Is there something philosophically wrong with the second way (bar2) of constructing a Bar object? It looks nicer to me, and does not require that stream1 variable that is only needed for a moment.

    Read the article

  • Serialization Error:Unable to generate a temporary class (result=1)

    - by ltech
    Running XSD.exe on my xml to generate C# class. All works well except on this property [System.Xml.Serialization.XmlArrayAttribute(Form=System.Xml.Schema.XmlSchemaForm.Unqualified)] [System.Xml.Serialization.XmlArrayItemAttribute("ATTRIBUTES", typeof(DOCUMENTDocumentATTRIBUTES), Form=System.Xml.Schema.XmlSchemaForm.Unqualified, IsNullable=false)] public DocumentATTRIBUTES[][] Document { get { return this.documentField; } set { this.documentField = value; } } I want to try and use CollectionBase, and this was my attempt public DocumentATTRIBUTESCollection Document { get { return this.documentField; } set { this.documentField = value; } } /// <remarks/> [System.SerializableAttribute()] [System.Diagnostics.DebuggerStepThroughAttribute()] [System.ComponentModel.DesignerCategoryAttribute("code")] [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)] public partial class DocumentATTRIBUTES { private string _author; private string _maxVersions; private string _summary; /// <remarks/> [System.Xml.Serialization.XmlElementAttribute(Form = System.Xml.Schema.XmlSchemaForm.Unqualified)] public string author { get { return _author; } set { _author = value; } } /// <remarks/> [System.Xml.Serialization.XmlElementAttribute(Form = System.Xml.Schema.XmlSchemaForm.Unqualified)] public string max_versions { get { return _maxVersions; } set { _maxVersions = value; } } /// <remarks/> [System.Xml.Serialization.XmlElementAttribute(Form = System.Xml.Schema.XmlSchemaForm.Unqualified)] public string summary { get { return _summary; } set { _summary = value; } } } public class DocumentAttributeCollection : System.Collections.CollectionBase { public DocumentAttributeCollection() : base() { } public DocumentATTRIBUTES this[int index] { get { return (DocumentATTRIBUTES)this.InnerList[index]; } } public void Insert(int index, DocumentATTRIBUTES value) { this.InnerList.Insert(index, value); } public int Add(DocumentATTRIBUTES value) { return (this.InnerList.Add(value)); } } However when I try to serialize my object using XmlSerializer serializer = new XmlSerializer(typeof(DocumentMetaData)); I get the error: {"Unable to generate a temporary class (result=1).\r\nerror CS0030: Cannot convert type 'DocumentATTRIBUTES' to 'DocumentAttributeCollection'\r\nerror CS1502: The best overloaded method match for 'DocumentAttributeCollection.Add(DocumentATTRIBUTES)' has some invalid arguments\r\nerror CS1503: Argument '1': cannot convert from 'DocumentAttributeCollection' to 'DocumentATTRIBUTES'\r\n"} the XSD pertaining to this property is <xs:complexType> <xs:sequence> <xs:element name="ATTRIBUTES" minOccurs="0" maxOccurs="unbounded"> <xs:complexType> <xs:sequence> <xs:element name="author" type="xs:string" minOccurs="0" /> <xs:element name="max_versions" type="xs:string" minOccurs="0" /> <xs:element name="summary" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element>

    Read the article

  • Serialization Error:Unable to generate a temporary class (result=1).\r\nerror CS0030:- c#

    - by ltech
    Running XSD.exe on my xml to generate C# class. All works well except on this property public DocumentATTRIBUTES[][] Document { get { return this.documentField; } set { this.documentField = value; } } I want to try and use CollectionBase, and this was my attempt public DocumentATTRIBUTESCollection Document { get { return this.documentField; } set { this.documentField = value; } } /// <remarks/> [System.SerializableAttribute()] [System.Diagnostics.DebuggerStepThroughAttribute()] [System.ComponentModel.DesignerCategoryAttribute("code")] [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)] public partial class DocumentATTRIBUTES { private string _author; private string _maxVersions; private string _summary; /// <remarks/> [System.Xml.Serialization.XmlElementAttribute(Form = System.Xml.Schema.XmlSchemaForm.Unqualified)] public string author { get { return _author; } set { _author = value; } } /// <remarks/> [System.Xml.Serialization.XmlElementAttribute(Form = System.Xml.Schema.XmlSchemaForm.Unqualified)] public string max_versions { get { return _maxVersions; } set { _maxVersions = value; } } /// <remarks/> [System.Xml.Serialization.XmlElementAttribute(Form = System.Xml.Schema.XmlSchemaForm.Unqualified)] public string summary { get { return _summary; } set { _summary = value; } } } public class DocumentAttributeCollection : System.Collections.CollectionBase { public DocumentAttributeCollection() : base() { } public DocumentATTRIBUTES this[int index] { get { return (DocumentATTRIBUTES)this.InnerList[index]; } } public void Insert(int index, DocumentATTRIBUTES value) { this.InnerList.Insert(index, value); } public int Add(DocumentATTRIBUTES value) { return (this.InnerList.Add(value)); } } However when I try to serialize my object using XmlSerializer serializer = new XmlSerializer(typeof(DocumentMetaData)); I get the error: {"Unable to generate a temporary class (result=1).\r\nerror CS0030: Cannot convert type 'DocumentATTRIBUTES' to 'DocumentAttributeCollection'\r\nerror CS1502: The best overloaded method match for 'DocumentAttributeCollection.Add(DocumentATTRIBUTES)' has some invalid arguments\r\nerror CS1503: Argument '1': cannot convert from 'DocumentAttributeCollection' to 'DocumentATTRIBUTES'\r\n"} the XSD pertaining to this property is <xs:complexType> <xs:sequence> <xs:element name="ATTRIBUTES" minOccurs="0" maxOccurs="unbounded"> <xs:complexType> <xs:sequence> <xs:element name="author" type="xs:string" minOccurs="0" /> <xs:element name="max_versions" type="xs:string" minOccurs="0" /> <xs:element name="summary" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element>

    Read the article

  • SQL Server 2008 - Update a temporary table

    - by user336786
    Hello, I have stored procedure in which I am trying to retrieve the last ticket completed by each user listed in a comma-delimited string of usernames. The user may not have a ticket associated with them, in this case I know that i just need to return null. The two tables that I am working with are defined as follows: User ---- UserName, FirstName, LastName Ticket ------ ID, CompletionDateTime, AssignedTo, AssignmentDate, StatusID TicketStatus ------------ ID, Comments I have created a stored procedure in which I am trying to return the last completed ticket for a comma-delimited list of usernames. Each record needs to include the comments associated with it. Currently, I'm trying the following: CREATE TABLE #Tickets ( [UserName] nvarchar(256), [FirstName] nvarchar(256), [LastName] nvarchar(256), [TicketID] int, [DateCompleted] datetime, [Comments] text ) -- This variable is actually passed into the procedure DECLARE @userList NVARCHAR(max) SET @userList='user1,user2,user2' -- Obtain the user information for each user INSERT INTO #Tickets ( [UserName], [FirstName], [LastName] ) SELECT u.[UserName], u.[FirstName], u.[LastName] FROM User u INNER JOIN dbo.ConvertCsvToTable(@userList) l ON u.UserName=l.item At this point, I have the username, first and last name for each user passed in. However, I do not know how to actually get the last ticket completed for each of these users. How do I do this? I believe I should be updating the temp table I have created. At the same time, id do not know how to get just the last record in an update statement. Thank you!

    Read the article

  • Getting a Temporary Table Returned from from Dynamic SQL in SQL Server 05, and parsing

    - by gloomy.penguin
    So I was requested to make a few things.... (it is Monday morning and for some reason this whole thing is turning out to be really hard for me to explain so I am just going to try and post a lot of my code; sorry) First, I needed a table: CREATE TABLE TICKET_INFORMATION ( TICKET_INFO_ID INT IDENTITY(1,1) NOT NULL, TICKET_TYPE INT, TARGET_ID INT, TARGET_NAME VARCHAR(100), INFORMATION VARCHAR(MAX), TIME_STAMP DATETIME DEFAULT GETUTCDATE() ) -- insert this row for testing... INSERT INTO TICKET_INFORMATION (TICKET_TYPE, TARGET_ID, TARGET_NAME, INFORMATION) VALUES (1,1,'RT_ID','IF_ID,int=1&IF_ID,int=2&OTHER,varchar(10)=val,ue3&OTHER,varchar(10)=val,ue4') The Information column holds data that needs to be parsed into a table. This is where I am having problems. In the resulting table, Target_Name needs to become a column that holds Target_ID as a value for each row in the resulting table. The string that needs to be parsed is in this format: @var_name1,@var_datatype1=@var_value1&@var_name2,@var_datatype2=@var_value2&@var_name3,@var_datatype3=@var_value3 And what I ultimately need as a result (in a table or table variable): RT_ID IF_ID OTHER 1 1 val,ue3 1 2 val,ue3 1 1 val,ue4 1 2 val,ue4 And I need to be able to join on the result. Initially, I was just going to make this a function that returns a table variable but for some reason I can't figure out how to get it into an actual table variable. Whatever parses the string needs to be able to be used directly in queries so I don't think a stored procedure is really the right thing to be using. This is the code that parses the Information string... it returns in a temporary table. -- create/empty temp table for var_name, var_type and var_value fields if OBJECT_ID('tempdb..#temp') is not null drop table #temp create table #temp (row int identity(1,1), var_name varchar(max), var_type varchar(30), var_value varchar(max)) -- just setting stuff up declare @target_name varchar(max), @target_id varchar(max), @info varchar(max) set @target_name = (select target_name from ticket_information where ticket_info_id = 1) set @target_id = (select target_id from ticket_information where ticket_info_id = 1) set @info = (select information from ticket_information where ticket_info_id = 1) --print @info -- some of these variables are re-used later declare @col_type varchar(20), @query varchar(max), @select as varchar(max) set @query = 'select ' + @target_id + ' as ' + @target_name + ' into #target; ' set @select = 'select * into ##global_temp from #target' declare @var_name varchar(100), @var_type varchar(100), @var_value varchar(100) declare @comma_pos int, @equal_pos int, @amp_pos int set @comma_pos = 1 set @equal_pos = 1 set @amp_pos = 0 -- while loop to parse the string into a table while @amp_pos < len(@info) begin -- get new comma position set @comma_pos = charindex(',',@info,@amp_pos+1) -- get new equal position set @equal_pos = charindex('=',@info,@amp_pos+1) -- set stuff that is going into the table set @var_name = substring(@info,@amp_pos+1,@comma_pos-@amp_pos-1) set @var_type = substring(@info,@comma_pos+1,@equal_pos-@comma_pos-1) -- get new ampersand position set @amp_pos = charindex('&',@info,@amp_pos+1) if @amp_pos=0 or @amp_pos<@equal_pos set @amp_pos = len(@info)+1 -- set last variable for insert into table set @var_value = substring(@info,@equal_pos+1,@amp_pos-@equal_pos-1) -- put stuff into the temp table insert into #temp (var_name, var_type, var_value) values (@var_name, @var_type, @var_value) -- is this a new field? if ((select count(*) from #temp where var_name = (@var_name)) = 1) begin set @query = @query + ' create table #' + @var_name + '_temp (' + @var_name + ' ' + @var_type + '); ' set @select = @select + ', #' + @var_name + '_temp ' end set @query = @query + ' insert into #' + @var_name + '_temp values (''' + @var_value + '''); ' end if OBJECT_ID('tempdb..##global_temp') is not null drop table ##global_temp exec (@query + @select) --select @query --select @select select * from ##global_temp Okay. So, the result I want and need is now in ##global_temp. How do I put all of that into something that can be returned from a function (or something)? Or can I get something more useful returned from the exec statement? In the end, the results of the parsed string need to be in a table that can be joined on and used... Ideally this would have been a view but I guess it can't with all the processing that needs to be done on that information string. Ideas? Thanks!

    Read the article

  • Mysql - help me optimize this query

    - by sandeepan-nath
    About the system: -The system has a total of 8 tables - Users - Tutor_Details (Tutors are a type of User,Tutor_Details table is linked to Users) - learning_packs, (stores packs created by tutors) - learning_packs_tag_relations, (holds tag relations meant for search) - tutors_tag_relations and tags and orders (containing purchase details of tutor's packs), order_details linked to orders and tutor_details. For a more clear idea about the tables involved please check the The tables section in the end. -A tags based search approach is being followed.Tag relations are created when new tutors register and when tutors create packs (this makes tutors and packs searcheable). For details please check the section How tags work in this system? below. Following is a simpler representation (not the actual) of the more complex query which I am trying to optimize:- I have used statements like explanation of parts in the query select SUM(DISTINCT( t.tag LIKE "%Dictatorship%" )) as key_1_total_matches, SUM(DISTINCT( t.tag LIKE "%democracy%" )) as key_2_total_matches, td., u., count(distinct(od.id_od)), if (lp.id_lp > 0) then some conditional logic on lp fields else 0 as tutor_popularity from Tutor_Details AS td JOIN Users as u on u.id_user = td.id_user LEFT JOIN Learning_Packs_Tag_Relations AS lptagrels ON td.id_tutor = lptagrels.id_tutor LEFT JOIN Learning_Packs AS lp ON lptagrels.id_lp = lp.id_lp LEFT JOIN `some other tables on lp.id_lp - let's call learning pack tables set (including Learning_Packs table)` LEFT JOIN Order_Details as od on td.id_tutor = od.id_author LEFT JOIN Orders as o on od.id_order = o.id_order LEFT JOIN Tutors_Tag_Relations as ttagrels ON td.id_tutor = ttagrels.id_tutor JOIN Tags as t on (t.id_tag = ttagrels.id_tag) OR (t.id_tag = lptagrels.id_tag) where some condition on Users table's fields AND CASE WHEN ((t.id_tag = lptagrels.id_tag) AND (lp.id_lp 0)) THEN `some conditions on learning pack tables set` ELSE 1 END AND CASE WHEN ((t.id_tag = wtagrels.id_tag) AND (wc.id_wc 0)) THEN `some conditions on webclasses tables set` ELSE 1 END AND CASE WHEN (od.id_od0) THEN od.id_author = td.id_tutor and some conditions on Orders table's fields ELSE 1 END AND ( t.tag LIKE "%Dictatorship%" OR t.tag LIKE "%democracy%") group by td.id_tutor HAVING key_1_total_matches = 1 AND key_2_total_matches = 1 order by tutor_popularity desc, u.surname asc, u.name asc limit 0,20 ===================================================================== What does the above query do? Does AND logic search on the search keywords (2 in this example - "Democracy" and "Dictatorship"). Returns only those tutors for which both the keywords are present in the union of the two sets - tutors details and details of all the packs created by a tutor. To make things clear - Suppose a Tutor name "Sandeepan Nath" has created a pack "My first pack", then:- Searching "Sandeepan Nath" returns Sandeepan Nath. Searching "Sandeepan first" returns Sandeepan Nath. Searching "Sandeepan second" does not return Sandeepan Nath. ====================================================================================== The problem The results returned by the above query are correct (AND logic working as per expectation), but the time taken by the query on heavily loaded databases is like 25 seconds as against normal query timings of the order of 0.005 - 0.0002 seconds, which makes it totally unusable. It is possible that some of the delay is being caused because all the possible fields have not yet been indexed, but I would appreciate a better query as a solution, optimized as much as possible, displaying the same results ========================================================================================== How tags work in this system? When a tutor registers, tags are entered and tag relations are created with respect to tutor's details like name, surname etc. When a Tutors create packs, again tags are entered and tag relations are created with respect to pack's details like pack name, description etc. tag relations for tutors stored in tutors_tag_relations and those for packs stored in learning_packs_tag_relations. All individual tags are stored in tags table. ==================================================================== The tables Most of the following tables contain many other fields which I have omitted here. CREATE TABLE IF NOT EXISTS users ( id_user int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(100) NOT NULL DEFAULT '', surname varchar(155) NOT NULL DEFAULT '', PRIMARY KEY (id_user) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=636 ; CREATE TABLE IF NOT EXISTS tutor_details ( id_tutor int(10) NOT NULL AUTO_INCREMENT, id_user int(10) NOT NULL DEFAULT '0', PRIMARY KEY (id_tutor), KEY Users_FKIndex1 (id_user) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=51 ; CREATE TABLE IF NOT EXISTS orders ( id_order int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id_order), KEY Orders_FKIndex1 (id_user), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=275 ; ALTER TABLE orders ADD CONSTRAINT Orders_ibfk_1 FOREIGN KEY (id_user) REFERENCES users (id_user) ON DELETE NO ACTION ON UPDATE NO ACTION; CREATE TABLE IF NOT EXISTS order_details ( id_od int(10) unsigned NOT NULL AUTO_INCREMENT, id_order int(10) unsigned NOT NULL DEFAULT '0', id_author int(10) NOT NULL DEFAULT '0', PRIMARY KEY (id_od), KEY Order_Details_FKIndex1 (id_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=284 ; ALTER TABLE order_details ADD CONSTRAINT Order_Details_ibfk_1 FOREIGN KEY (id_order) REFERENCES orders (id_order) ON DELETE NO ACTION ON UPDATE NO ACTION; CREATE TABLE IF NOT EXISTS learning_packs ( id_lp int(10) unsigned NOT NULL AUTO_INCREMENT, id_author int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (id_lp), KEY Learning_Packs_FKIndex2 (id_author), KEY id_lp (id_lp) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=23 ; CREATE TABLE IF NOT EXISTS tags ( id_tag int(10) unsigned NOT NULL AUTO_INCREMENT, tag varchar(255) DEFAULT NULL, PRIMARY KEY (id_tag), UNIQUE KEY tag (tag), KEY id_tag (id_tag), KEY tag_2 (tag), KEY tag_3 (tag) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3419 ; CREATE TABLE IF NOT EXISTS tutors_tag_relations ( id_tag int(10) unsigned NOT NULL DEFAULT '0', id_tutor int(10) DEFAULT NULL, KEY Tutors_Tag_Relations (id_tag), KEY id_tutor (id_tutor), KEY id_tag (id_tag) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE tutors_tag_relations ADD CONSTRAINT Tutors_Tag_Relations_ibfk_1 FOREIGN KEY (id_tag) REFERENCES tags (id_tag) ON DELETE NO ACTION ON UPDATE NO ACTION; CREATE TABLE IF NOT EXISTS learning_packs_tag_relations ( id_tag int(10) unsigned NOT NULL DEFAULT '0', id_tutor int(10) DEFAULT NULL, id_lp int(10) unsigned DEFAULT NULL, KEY Learning_Packs_Tag_Relations_FKIndex1 (id_tag), KEY id_lp (id_lp), KEY id_tag (id_tag) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE learning_packs_tag_relations ADD CONSTRAINT Learning_Packs_Tag_Relations_ibfk_1 FOREIGN KEY (id_tag) REFERENCES tags (id_tag) ON DELETE NO ACTION ON UPDATE NO ACTION; =================================================================================== Following is the exact query (this includes classes also - tutors can create classes and search terms are matched with classes created by tutors):- select count(distinct(od.id_od)) as tutor_popularity, CASE WHEN (IF((wc.id_wc 0), ( wc.wc_api_status = 1 AND wc.wc_type = 0 AND wc.class_date '2010-06-01 22:00:56' AND wccp.status = 1 AND (wccp.country_code='IE' or wccp.country_code IN ('INT'))), 0)) THEN 1 ELSE 0 END as 'classes_published', CASE WHEN (IF((lp.id_lp 0), (lp.id_status = 1 AND lp.published = 1 AND lpcp.status = 1 AND (lpcp.country_code='IE' or lpcp.country_code IN ('INT'))),0)) THEN 1 ELSE 0 END as 'packs_published', td . * , u . * from Tutor_Details AS td JOIN Users as u on u.id_user = td.id_user LEFT JOIN Learning_Packs_Tag_Relations AS lptagrels ON td.id_tutor = lptagrels.id_tutor LEFT JOIN Learning_Packs AS lp ON lptagrels.id_lp = lp.id_lp LEFT JOIN Learning_Packs_Categories AS lpc ON lpc.id_lp_cat = lp.id_lp_cat LEFT JOIN Learning_Packs_Categories AS lpcp ON lpcp.id_lp_cat = lpc.id_parent LEFT JOIN Learning_Pack_Content as lpct on (lp.id_lp = lpct.id_lp) LEFT JOIN Webclasses_Tag_Relations AS wtagrels ON td.id_tutor = wtagrels.id_tutor LEFT JOIN WebClasses AS wc ON wtagrels.id_wc = wc.id_wc LEFT JOIN Learning_Packs_Categories AS wcc ON wcc.id_lp_cat = wc.id_wp_cat LEFT JOIN Learning_Packs_Categories AS wccp ON wccp.id_lp_cat = wcc.id_parent LEFT JOIN Order_Details as od on td.id_tutor = od.id_author LEFT JOIN Orders as o on od.id_order = o.id_order LEFT JOIN Tutors_Tag_Relations as ttagrels ON td.id_tutor = ttagrels.id_tutor JOIN Tags as t on (t.id_tag = ttagrels.id_tag) OR (t.id_tag = lptagrels.id_tag) OR (t.id_tag = wtagrels.id_tag) where (u.country='IE' or u.country IN ('INT')) AND CASE WHEN ((t.id_tag = lptagrels.id_tag) AND (lp.id_lp 0)) THEN lp.id_status = 1 AND lp.published = 1 AND lpcp.status = 1 AND (lpcp.country_code='IE' or lpcp.country_code IN ('INT')) ELSE 1 END AND CASE WHEN ((t.id_tag = wtagrels.id_tag) AND (wc.id_wc 0)) THEN wc.wc_api_status = 1 AND wc.wc_type = 0 AND wc.class_date '2010-06-01 22:00:56' AND wccp.status = 1 AND (wccp.country_code='IE' or wccp.country_code IN ('INT')) ELSE 1 END AND CASE WHEN (od.id_od0) THEN od.id_author = td.id_tutor and o.order_status = 'paid' and CASE WHEN (od.id_wc 0) THEN od.can_attend_class=1 ELSE 1 END ELSE 1 END AND 1 group by td.id_tutor order by tutor_popularity desc, u.surname asc, u.name asc limit 0,20 Please note - The provided database structure does not show all the fields and tables as in this query

    Read the article

  • iPad: Tables in Popover Views do not Scroll to Show Selected Row

    - by mahboudz
    I am having two problems with viewcontrollerss in landscape orientation on the iPad. (1) I have two popups which hold tables. The tables should scroll to a specific row to reflect a selection in the main view. Instead, the tables do scroll down some but the actual selected row remains off screen. (2) All my action sheets come up with a width of 320. In Interface Builder, all my views are created in landscape orientation. Only the main Window is not, but I don't see a way to change that. My Configuration: Upon launch, I get the following coordinates for my main window and the main viewcontroller view: Window frame {{0, 0}, {768, 1024}} mainView frame {{0, 0}, {748, 1024}} All other views after that show these coordinates when summoned (when loaded but before being presented): frame of keysig {{0, 0}, {1024, 768}} frame of instrumentSelect {{20, 0}, {1024, 768}} frame of settings {{0, 0}, {467, 300}} In all my viewControllers, i respond to shouldAutorotateToInterfaceOrientation with: return ((interfaceOrientation == UIInterfaceOrientationLandscapeLeft) || (interfaceOrientation == UIInterfaceOrientationLandscapeRight)); Everything (almost) functions as expected. The app launches into one of the two landscape modes. The views (and viewcontrollers) display everything where it belongs and taps work all across the screen as expected. However, I still have the two problems. Problem 1: I have two popups containing tables long enough to run off screen. The tables should scroll to a selected row. They do scroll i.e. they don't start visually at row 1 but they don't scroll enough to actually show the selected row. It almost seems like a UITable internal rect gets created with the wrong number and stays that way but I've checked both of the UITableView's scrollView content coordinates and they seemed reasonable. Problem 2: I think this is related to problem 1 because my actionsheets come up with a width of 320. I can only assume that the iPad allows actionSheets in only 320 or 480 widths and since it somehow thinks that the screen is oriented in portrait mode, it uses the narrower width. There you have it. I can't believe I am still getting hung up on orientation issues. I swear Apple doesn't make it easy to have a landscape app. Any ideas?

    Read the article

  • Complex relationship between tables in NHibernate

    - by Ilya Kogan
    Hi all, I'm writing a Fluent NHibernate mapping for a legacy Oracle database. The challenge is that the tables have composite primary keys. If I were at total freedom, I would redesign the relationships and auto-generate primary keys, but other applications must write to the same database and read from it, so I cannot do it. These are the two tables I'll focus on: Example data Trips table: 1, 10:00, 11:00 ... 1, 12:00, 15:00 ... 1, 16:00, 19:00 ... 2, 12:00, 13:00 ... 3, 9:00, 18:00 ... Faults table: 1, 13:00 ... 1, 23:00 ... 2, 12:30 ... In this case, vehicle 1 made three trips and has two faults. The first fault happened during the second trip, and the second fault happened while the vehicle was resting. Vehicle 2 had one trip, during which a fault happened. Constraints Trips of the same vehicle never overlap. So the tables have an optional one-to-many relationship, because every fault either happens during a trip or it doesn't. If I wanted to join them in SQL, I would write: select ... from Faults left outer join Trips on Faults.VehicleId = Trips.VehicleId and Faults.FaultTime between Trips.TripStartTime and Trips.TripEndTime and then I'd get a dataset where every fault appears exactly once (one-to-many as I said). Note that there is no Vehicles table, and I don't need one. But I did create a view that contains all VehicleIds from both tables, so I can use it as a junction table. What am I actually looking for? The tables are huge because they cover years of data, and every time I only need to fetch a range of a few hours. So I need a mapping and a criteria that will run something like the following SQL underneath: select ... from Faults left outer join Trips on Faults.VehicleId = Trips.VehicleId and Faults.FaultTime between Trips.TripStartTime and Trips.TripEndTime where Faults.FaultTime between :p0 and :p1 Do you have any ideas how to achieve it? Note 1: Currently the application shouldn't write to the database, so persistence is not a must, although if the mapping supports persistence, it may help at some point in the future. Note 2: I know it's a tough one, so if you give me a great answer, you will be properly rewarded :) Thank you for reading this long question, and now I only hope for the best :)

    Read the article

  • data from few MySQL tables sorted by ASC

    - by Andrew
    In the dbase I 've few tables named as aaa_9xxx, aaa_9yyy, aaa_9zzz. I want to find all data with a specified DATE and show it with the TIME ASC. First, I must find a tables in the dbase: $STH_1a = $DBH->query("SELECT table_name FROM information_schema.tables WHERE table_name LIKE 'aaa\_9%' "); foreach($STH_1a as $row) { $table_name_s1[] = $row['table_name']; } Second, I must find a data wit a concrete date and show it with TIME ASC: foreach($table_name_s1 as $table_name_1) { $STH_1a2 = $DBH->query("SELECT * FROM `$table_name_1` WHERE date = '2011-11-11' ORDER BY time ASC "); while ($row = $STH_1a2->fetch(PDO::FETCH_ASSOC)) { echo " ".$table_name_1."-".$row['time']."-".$row['ei_name']." <br>"; } } .. but it shows the data sorted by tables name, then by TIME ASC. I must to have all this data (from all tables) sorted by TIME ASC. Thank You dev-null-dweller, Andrew Stubbs and Jaison Erick for your help. I test the Erick solution : foreach($STH_1a as $row) { $stmts[] = sprintf('SELECT * FROM %s WHERE date="%s"', $row['table_name'], '2011-11-11'); } $stmt = implode("\nUNION\n", $stmts); $stmt .= "\nORDER BY time ASC"; $STH_1a2 = $DBH->query($stmt); while ($row_1a2 = $STH_1a2->fetch(PDO::FETCH_ASSOC)) { echo " ".$row['table_name']."-".$row_1a2['time']."-".$row_1a2['ei_name']." <br>"; } it's working but I've problem with 'table_name' - it's always the LAST table name. //---------------------------------------------------------------------- end the ending solution with all fixes, thanks all for your help, :)) foreach($STH_1a as $row) { $stmts[] = sprintf("SELECT *, '%s' AS table_name FROM %s WHERE date='%s'", $row['table_name'], $row['table_name'], '2011-11- 11'); } $stmt = implode("\nUNION\n", $stmts); $stmt .= "\nORDER BY time ASC"; $STH_1a2 = $DBH->query($stmt); while ($row_1a2 = $STH_1a2->fetch(PDO::FETCH_ASSOC)) { echo " ".$row_1a2['table_name']."-".$row_1a2['time']."-".$row_1a2['ei_name']." <br>"; }

    Read the article

  • CHKDSK: What option DOES NOT delete files and turn them into .chk files?

    - by CHKDSKuser
    I had a recent power outage while using my computer, with a 1TB hard drive being directly accessed as the power went out. When the power came back on, and I rebooted my computer, one of my 1TB hard drives would not register with WinXP SP3, and showed a Total Space of 0, and an Available Space of 0. The file system (NTFS) also did not register...every entry for the drive was either blank or zeroed. My assumption is that the file tables were damaged/corrupted because the drive was being directly accessed when the power went out. After doing some research, I ran CHKDSK with whatever default options it runs with (I'm not sure what they are as I didn't see them displayed). Upon completion of CHKDSK, the drive registered with WinXP as a 1TB hard drive, with an accurately-reflected amount of available space. But CHKDSK also deleted about 16GB of files from their original directories, and changed them all into sequentially-named *.chk files. My question is how can CHKDSK be run in a situation like mine where the file tables needed to be restored, but without having CHKDSK delete any files from their original directories, even if they may be damaged/corrupt? I'd simply like to be able to run CHKDSK and have it restore the file tables, and repair bad sector damage, as it did, but not have it do anything else such as delete files and convert them to CHK files. Any ideas? Or is there a CHKDSK alternative that can perform the same functions without the file deletions?

    Read the article

  • JSON object array to store data of a form in local storage temporary (PhoneGap project)

    - by Nadeesha
    I am building a data aqusition system using PhoneGap. .I am trying to store my form data temporary on local storage using JSON,Data should be visible after I close and reopen the application (after pressing Get Data button),But after I close it only the lastly entered record is visible This is my code <!DOCTYPE html> <html> <head> <title>Household Profile DB storage</title> <meta charset="utf-8"> <meta name="viewport" content="user-scalable=no, initial-scale=1, maximum-scale=1, minimum-scale=1,width=device-width" /> <link rel="stylesheet" href="jquery.mobile-1.4.2/jquery.mobile-1.4.2.min.css"> <link rel="stylesheet" href="css/table.css"> <script type="text/javascript" src="js/jquery-1.9.1.min.js"></script> <script type="text/javascript" src="jquery.mobile-1.4.2/jquery.mobile-1.4.2.min.js"></script> <script type="text/javascript" src="js/iscroll.js"></script> <script type="text/javascript" charset="utf-8"> function onDeviceReady() { persistData(homeId,owner,gramaND,contactNo,address,race); } function saveLocal(form){ if (window.localStorage) { var fhomeId = form.homeId.value, fowner = form.owner.value, fgramaND = form.gramaND.value, fcontactNo= form.contactNo.value, faddress = form.address.value, frace = form.race.value; alert("hi"); var highscores = [{"homeId": fhomeId, "owner":fowner, "gramaND":fgramaND, "contactNo":fcontactNo, "address":faddress, "race":frace}]; localStorage.setItem("highscores",JSON.stringify(highscores)); alert("The data has been stored successfully."); } else { alert("Your Browser does not support LocalStorage."); } } function readLocal(){ if (window.localStorage) { var scores =[]; //Get the highscores object scores = localStorage.getItem("highscores"); scores = JSON.parse(scores); for (i=0;i<scores.length;i++){ var text = "homeId :"+scores[i].homeId +"<br>"+ "owner:"+ scores[i].owner+"<br>"+ "address"+scores[i].address +"<br>"+ "gramaND"+scores[i].gramaND +"<br>"+ "contactNo"+scores[i].contactNo+"<br>" + '<Button value="DELETE" onclick="'+scores.splice(i, 0)+'><>/Button>'; var tbodyx = document.getElementsByTagName("tbody"); var tr=document.createElement("TR"); var td=document.createElement("TD"); td.innerHTML = text; tr.appendChild(td); tbody.appendChild(tr); } } } </script> </head> <body> <div data-role="page" id="page1"> <!--/header--> <div data-role="header" data-position="inline" data-theme="b"> <a href="#" data-icon="back" data-rel="back" title="Go back">Back</a> <h1>Household Profile</h1> <a href="index.html" data-icon="home">Menu</a> </div> <!--/header--> <div id="wrapper"> <form id="userInput" action ="" method="GET"> <div data-role="content"> <div data-role="fieldcontain"> <label > Home ID </label> <input class="inputClass" id="homeId" placeholder="H0001" value="" data-mini="true" type="text"> </div> <div data-role="fieldcontain"> <label > Owner </label> <input class="inputClass" id="owner" placeholder="Aberathne" value="" type="text"> </div> <div data-role="fieldcontain"> <label class="select">GramaNiladhari Division</label> <select class="inputClass" id="gramaND"> <option value="GramaNiladhari Division 1">GramaNiladhari Division 1</option> <option value="GramaNiladhari Division 2">GramaNiladhari Division 2</option> <option value="GramaNiladhari Division 3">GramaNiladhari Division 3</option> <option value="GramaNiladhari Division 4">GramaNiladhari Division 4</option> </select> </div> <div data-role="fieldcontain"> <label > Contact No </label> <input class="inputClass" id="contactNo" placeholder="071-9545-073" value="" type="number"> </div> <div data-role="fieldcontain"> <label >Address:</label> <textarea cols="40" rows="8" class="inputClass" id="address"></textarea> </div> <div class="ui-block-a"><button type="submit" data-theme="d">Location in a Map</button></div> <div data-role="fieldcontain"> <label >Race</label> <select class="inputClass" id="race"> <option value=" Sinhalese"> Sinhalese</option> <option value=" Sri Lanka Tamils"> Sri Lanka Tamils</option> <option value=" Moors"> Moors</option> <option value=" Indian Tamils "> Indian Tamils </option> <option value=" Malays "> Malays </option> <option value=" Burghers "> Burghers </option> </select> </div> <input class="buttonClass" type="button" value="Insert Data" onclick="saveLocal(this.form);"> </div> </form> </div> <input class="buttonClass" type="button" value="get Data" onclick="readLocal();"> <!-- <p id="dhomeId"></p> <p id="downer"></p> <p id="dgramaND"></p> <p id="dcontactNo"></p> <p id="daddress"></p> <p id="drace"></p>--> <table border="1"> <tbody id="tbody"> <tr><td>test1</td></tr> <tr><td>test2</td></tr> </tbody> </table> </div> </body> </html> Also I need to expand my code to edit and delete record from local storage.

    Read the article

< Previous Page | 13 14 15 16 17 18 19 20 21 22 23 24  | Next Page >