Search Results

Search found 18695 results on 748 pages for 'query manipulation'.

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

• Basic date/time manipulation in NHiberate query

  - by Yann Trevin

  I'm trying to restrict my NHibernate query with some basic date/time manipulation. More specifically, I want to execute the following statement (pseudo-SQL): select * from article where created_on + lifespan >= sysdate with: created_on is mapped to a property of type DateTime. lifespan is mapped to a property of type TimeSpan. sysdate is the current date/time (of the database server or ofthe application host, I don't care) Is there any built-in way to do that by using the Criteria-API or HQL? return session .CreateCriteria<Article>() .Add( ? ) .List<Article>();

  Read the article

• string manipulation without alloc mem in c

  - by Mike

  I'm wondering if there is another way of getting a sub string without allocating memory. To be more specific, I have a string as: const char *str = "9|0\" 940 Hello"; Currently I'm getting the 940, which is the sub-string I want as, char *a = strstr(str,"9|0\" "); char *b = substr(a+5, 0, 3); // gives me the 940 Where substr is my sub string procedure. The thing is that I don't want to allocate memory for this by calling the sub string procedure. Is there a much easier way?, perhaps by doing some string manipulation and not alloc mem. I'll appreciate any feedback.

  Read the article

• Storing cvs data for further manipulation using Ruby

  - by ischnura

  I am dealing with a csv file that has some customer information (email, name, address, amount, [shopping_list: item 1, item 2]). I would like work with the data and produce some labels for printing... as well as to gather some extra information (total amounts, total items 1...) My main concern is to find the appropriate structure to store the data in ruby for future manipulation. For now I have thought about the following possibilities: multidimensional arrays: pretty simple to build, but pretty hard to access the data in a beautiful ruby way. hashes: having the email as key, and storing the information in different hashes (one hash for name, another hash for address, another hash for shopping list...) (getting the cvs data in to a Database and working with the data from ruby??) I would really appreciate your advice and guidance!!

  Read the article

• String Manipulation in Bash

  - by user348000

  Hello- I am a newbie in Bash and I am doing some string manipulation. I have the following file among other files in my directory: jdk-6u20-solaris-i586.sh I am doing the following to get jdk-6u20 in my script: myvar=`ls -la | awk '{print $9}' | egrep "i586" | cut -c1-8` echo $myvar but now I want to convert jdk-6u20 to jdk1.6.0_20. I can't seem to figure out how to do it. It must be as generic as possible. For example if I had jdk-6u25, I should be able to convert it at the same way to jdk1.6.0_25 so on and so forth Any suggestions?

  Read the article

• could not resolve property (complex properties)

  - by felipeoriani

  I have a asp.net mvc application with NHibernate and I do not know how to resolve a problem to query some data. I have this query: // create query var query = session.QueryOVer<Laudo>().Fetch(x => x.Equipament).Eager; // add some filters if (idEquipament.HasValue) query = query.And(x => x.Equipament.Id == idEquipament.Value); //I got the error here... if (idCompany.HasValue) query = query.And(x => x.Equipament.Company.Id == idCompany.Value); When I try to execute this query, I've got an exception with this message: "could not resolve property: Equipament.Company.Id of: DomainModel.Laudo" what can I do to fix this problem? Thanks

  Read the article

• SQL SEVER – Finding Memory Pressure – External and Internal

  - by pinaldave

  Following query will provide details of external and internal memory pressure. It will return the data how much portion in the existing memory is assigned to what kind of memory type. SELECT TYPE, SUM(single_pages_kb) InternalPressure, SUM(multi_pages_kb) ExtermalPressure FROM sys.dm_os_memory_clerks GROUP BY TYPE ORDER BY SUM(single_pages_kb) DESC, SUM(multi_pages_kb) DESC GO What is your method to find memory pressure? Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL Optimization, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – Challenge – Puzzle – Usage of FAST Hint

  - by pinaldave

  I was recently working with various SQL Server Hints. After working for a day on various hints, I realize that for one hint, I am not able to come up with good example. The hint is FAST. Let us look at the definition of the FAST hint from the Book On-Line. FAST number_rows Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set. Now the question is in what condition this hint can be useful. I have tried so many different combination, I have found this hint does not make much performance difference, infect I did not notice any change in time taken to load the resultset. I noticed that this hint does not change number of the page read to return result. Now when there is difference in performance is expected because if you read the what FAST hint does is that it only returns first few results FAST – which does not mean there will be difference in performance. I also understand that this hint gives the guidance/suggestions/hint to query optimizer that there are only 100 rows are in expected resultset. This tricking the optimizer to think there are only 100 rows and which (may) lead to render different execution plan than the one which it would have taken in normal case (without hint). Again, not necessarily, this will happen always. Now if you read above discussion, you will find that basic understanding of the hint is very clear to me but I still feel that I am missing something. Here are my questions: 1) In what condition this hint can be useful? What is the case, when someone want to see first few rows early because my experience suggests that when first few rows are rendered remaining rows are rendered as well. 2) Is there any way application can retrieve the fast fetched rows from SQL Server? 3) Do you use this hint in your application? Why? When? and How? Here are few examples I have attempted during the my experiment and found there is no difference in execution plan except its estimated number of rows are different leading optimizer think that the cost is less but in reality that is not the case. USE AdventureWorks GO SET STATISTICS IO ON SET STATISTICS TIME ON GO --------------------------------------------- -- Table Scan with Fast Hint SELECT * FROM Sales.SalesOrderDetail GO SELECT * FROM Sales.SalesOrderDetail OPTION (FAST 100) GO --------------------------------------------- -- Table Scan with Where on Index Key SELECT * FROM Sales.SalesOrderDetail WHERE OrderQty = 14 GO SELECT * FROM Sales.SalesOrderDetail WHERE OrderQty = 14 OPTION (FAST 100) GO --------------------------------------------- -- Table Scan with Where on Index Key SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderDetailID < 1000 GO SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderDetailID < 1000 OPTION (FAST 100) GO Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – Tricks to Replace SELECT * with Column Names – SQL in Sixty Seconds #017 – Video

  - by pinaldave

  You might have heard many times that one should not use SELECT * as there are many disadvantages to the usage of the SELECT *. I also believe that there are always rare occasion when we need every single column of the query. In most of the cases, we only need a few columns of the query and we should retrieve only those columns. SELECT * has many disadvantages. Let me list a few and remaining you can add as a comment.  Retrieves unnecessary columns and increases network traffic When a new columns are added views needs to be refreshed manually Leads to usage of sub-optimal execution plan Uses clustered index in most of the cases instead of using optimal index It is difficult to debug. There are two quick tricks I have discussed in the video which explains how users can avoid using SELECT * but instead list the column names. 1) Drag the columns folder from SQL Server Management Studio to Query Editor 2) Right Click on Table Name >> Script TAble AS >> SELECT To… >> Select option It is extremely easy to list the column names in the table. In today’s sixty seconds video, you will notice that I was able to demonstrate both the methods very quickly. From now onwards there should be no excuse for not listing ColumnName. Let me ask a question back – is there ever a reason to SELECT *? If yes, would you please share that as a comment. More on SELECT *: SQL SERVER – Solution – Puzzle – SELECT * vs SELECT COUNT(*) SQL SERVER – Puzzle – SELECT * vs SELECT COUNT(*) SQL SERVER – SELECT vs. SET Performance Comparison I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can. If we like your idea we promise to share with you educational material. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Query, SQL Scripts, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology, Video

  Read the article

• SQL SERVER – A Puzzle – Fun with NULL – Fix Error 8117

  - by pinaldave

  During my 8 years of career, I have been involved in many interviews. Quite often, I act as the  interview. If I am the interviewer, I ask many questions – from easy questions to difficult ones. When I am the interviewee, I frequently get an opportunity to ask the interviewer some questions back. Regardless of the my capacity in attending the interview, I always make it a point to ask the interviewer at least one question. What is NULL? It’s always fun to ask this question during interviews, because in every interview, I get a different answer. NULL is often confused with false, absence of value or infinite value. Honestly, NULL is a very interesting subject as it bases its behavior in server settings. There are a few properties of NULL that are universal, but the knowledge about these properties is not known in a universal sense. Let us run this simple puzzle. Run the following T-SQL script: SELECT SUM(data) FROM (SELECT NULL AS data) t It will return the following error: Msg 8117, Level 16, State 1, Line 1 Operand data type NULL is invalid for sum operator. Now the error makes it very clear that NULL is invalid for sum Operator. Frequently enough, I have showed this simple query to many folks whom I came across. I asked them if they could modify the subquery and return the result as NULL. Here is what I expected: Even though this is a very simple looking query, so far I’ve got the correct answer from only 10% of the people to whom I have asked this question. It was common for me to receive this kind of answer – convert the NULL to some data type. However, doing so usually returns the value as 0 or the integer they passed. SELECT SUM(data) FROM (SELECT ISNULL(NULL,0) AS data) t I usually see many people modifying the outer query to get desired NULL result, but that is not allowed in this simple puzzle. This small puzzle made me wonder how many people have a clear understanding about NULL. Well, here is the answer to my simple puzzle. Just CAST NULL AS INT and it will return the final result as NULL: SELECT SUM(data) FROM (SELECT CAST(NULL AS INT) AS data) t Now that you know the answer, don’t you think it was very simple indeed? This blog post is especially dedicated to my friend Madhivanan who has written an excellent blog post about NULL. I am confident that after reading the blog post from Madhivanan, you will have no confusion regarding NULL in the future. Read: NULL, NULL, NULL and nothing but NULL. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – Find Referenced or Referencing Object in SQL Server using sys.sql_expression_dependencies

  - by pinaldave

  A very common question which I often receive are: How do I find all the tables used in a particular stored procedure? How do I know which stored procedures are using a particular table? Both are valid question but before we see the answer of this question – let us understand two small concepts – Referenced and Referencing. Here is the sample stored procedure. CREATE PROCEDURE mySP AS SELECT * FROM Sales.Customer GO Reference: The table Sales.Customer is the reference object as it is being referenced in the stored procedure mySP. Referencing: The stored procedure mySP is the referencing object as it is referencing Sales.Customer table. Now we know what is referencing and referenced object. Let us run following queries. I am using AdventureWorks2012 as a sample database. If you do not have SQL Server 2012 here is the way to get SQL Server 2012 AdventureWorks database. Find Referecing Objects of a particular object Here we are finding all the objects which are using table Customer in their object definitions (regardless of the schema). USE AdventureWorks GO SELECT referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID), referencing_object_name = o.name, referencing_object_type_desc = o.type_desc, referenced_schema_name, referenced_object_name = referenced_entity_name, referenced_object_type_desc = o1.type_desc, referenced_server_name, referenced_database_name --,sed.* -- Uncomment for all the columns FROM sys.sql_expression_dependencies sed INNER JOIN sys.objects o ON sed.referencing_id = o.[object_id] LEFT OUTER JOIN sys.objects o1 ON sed.referenced_id = o1.[object_id] WHERE referenced_entity_name = 'Customer' The above query will return all the objects which are referencing the table Customer. Find Referenced Objects of a particular object Here we are finding all the objects which are used in the view table vIndividualCustomer. USE AdventureWorks GO SELECT referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID), referencing_object_name = o.name, referencing_object_type_desc = o.type_desc, referenced_schema_name, referenced_object_name = referenced_entity_name, referenced_object_type_desc = o1.type_desc, referenced_server_name, referenced_database_name --,sed.* -- Uncomment for all the columns FROM sys.sql_expression_dependencies sed INNER JOIN sys.objects o ON sed.referencing_id = o.[object_id] LEFT OUTER JOIN sys.objects o1 ON sed.referenced_id = o1.[object_id] WHERE o.name = 'vIndividualCustomer' The above query will return all the objects which are referencing the table Customer. I am just glad to write above query. There are more to write to this subject. In future blog post I will write more in depth about other DMV which also aids in finding referenced data. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL DMV, SQL Query, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology

  Read the article

• SQL University: Parallelism Week - Part 3, Settings and Options

  - by Adam Machanic

  Congratulations! You've made it back for the the third and final installment of Parallelism Week here at SQL University . So far we've covered the fundamentals of multitasking vs. parallel processing and delved into how parallel query plans actually work . Today we'll take a look at the settings and options that influence intra-query parallelism and discuss how best to set things up in various situations. Instance-Level Configuration Your database server probably has more than one logical processor....(read more)

  Read the article

• SQL SERVER – Weekly Series – Memory Lane – #048

  - by Pinal Dave

  Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane. 2007 Order of Result Set of SELECT Statement on Clustered Indexed Table When ORDER BY is Not Used Above theory is true in most of the cases. However SQL Server does not use that logic when returning the resultset. SQL Server always returns the resultset which it can return fastest.In most of the cases the resultset which can be returned fastest is the resultset which is returned using clustered index. Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT One of the Jr. Developer asked me this question (What will be the Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT?) while I was rushing to an important meeting. I was getting late so I asked him to talk with his Application Tech Lead. When I came back from meeting both of them were looking for me. They said they are confused. I quickly wrote down following example for them. 2008 SQL SERVER – Guidelines and Coding Standards Complete List Download Coding standards and guidelines are very important for any developer on the path of a successful career. A coding standard is a set of guidelines, rules and regulations on how to write code. Coding standards should be flexible enough or should take care of the situation where they should not prevent best practices for coding. They are basically the guidelines that one should follow for better understanding. Download Guidelines and Coding Standards complete List Download Get Answer in Float When Dividing of Two Integer Many times we have requirements of some calculations amongst different fields in Tables. One of the software developers here was trying to calculate some fields having integer values and divide it which gave incorrect results in integer where accurate results including decimals was expected. Puzzle – Computed Columns Datatype Explanation SQL Server automatically does a cast to the data type having the highest precedence. So the result of INT and INT will be INT, but INT and FLOAT will be FLOAT because FLOAT has a higher precedence. If you want a different data type, you need to do an EXPLICIT cast. Renaming SP is Not Good Idea – Renaming Stored Procedure Does Not Update sys.procedures I have written many articles about renaming a tables, columns and procedures SQL SERVER – How to Rename a Column Name or Table Name, here I found something interesting about renaming the stored procedures and felt like sharing it with you all. The interesting fact is that when we rename a stored procedure using SP_Rename command, the Stored Procedure is successfully renamed. But when we try to test the procedure using sp_helptext, the procedure will be having the old name instead of new names. 2009 Insert Values of Stored Procedure in Table – Use Table Valued Function It is clear from the result set that , where I have converted stored procedure logic into the table valued function, is much better in terms of logic as it saves a large number of operations. However, this option should be used carefully. The performance of the stored procedure is “usually” better than that of functions. Interesting Observation – Index on Index View Used in Similar Query Recently, I was working on an optimization project for one of the largest organizations. While working on one of the queries, we came across a very interesting observation. We found that there was a query on the base table and when the query was run, it used the index, which did not exist in the base table. On careful examination, we found that the query was using the index that was on another view. This was very interesting as I have personally never experienced a scenario like this. In simple words, “Query on the base table can use the index created on the indexed view of the same base table.” Interesting Observation – Execution Plan and Results of Aggregate Concatenation Queries Working with SQL Server has never seemed to be monotonous – no matter how long one has worked with it. Quite often, I come across some excellent comments that I feel like acknowledging them as blog posts. Recently, I wrote an article on SQL SERVER – Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location, which is well received in the community. 2010 I encourage all of you to go through complete series and write your own on the subject. If you write an article and send it to me, I will publish it on this blog with due credit to you. If you write on your own blog, I will update this blog post pointing to your blog post. SQL SERVER – ORDER BY Does Not Work – Limitation of the View 1 SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the View 2 SQL SERVER – Index Created on View not Used Often – Limitation of the View 3 SQL SERVER – SELECT * and Adding Column Issue in View – Limitation of the View 4 SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5 SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6 SQL SERVER – Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7 SQL SERVER – Outer Join Not Allowed in Indexed Views – Limitation of the View 8 SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9 SQL SERVER – Keywords View Definition Must Not Contain for Indexed View – Limitation of the View 10 SQL SERVER – View Over the View Not Possible with Index View – Limitations of the View 11 2011 Startup Parameters Easy to Configure If you are a regular reader of this blog, you must be aware that I have written about SQL Server Denali recently. Here is the quickest way to reach into the screen where we can change the startup parameters. Go to SQL Server Configuration Manager >> SQL Server Services >> Right Click on the Server >> Properties >> Startup Parameters 2012 Validating Unique Columnname Across Whole Database I sometimes come across very strange requirements and often I do not receive a proper explanation of the same. Here is the one of those examples. For example “Our business requirement is when we add new column we want it unique across current database.” Read the solution to this strange request in this blog post. Excel Losing Decimal Values When Value Pasted from SSMS ResultSet It is very common when users are coping the resultset to Excel, the floating point or decimals are missed. The solution is very much simple and it requires a small adjustment in the Excel. By default Excel is very smart and when it detects the value which is getting pasted is numeric it changes the column format to accommodate that. Basic Calculation and PEMDAS Order of Operation Read this interesting blog post for fantastic conversation about the subject. Copy Column Headers from Resultset – SQL in Sixty Seconds #027 – Video http://www.youtube.com/watch?v=x_-3tLqTRv0 Delete From Multiple Table – Update Multiple Table in Single Statement There are two questions which I get every single day multiple times. In my gmail, I have created standard canned reply for them. Let us see the questions here. I want to delete from multiple table in a single statement how will I do it? I want to update multiple table in a single statement how will I do it? Read the answer in the blog post. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Memory Lane, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – BI Quiz Hint – Performance Tuning Cubes – Hints

  - by pinaldave

  I earlier wrote about SQL BI Quiz over here and here. The details of the quiz is here: Working with huge data is very common when it is about Data Warehousing. It is necessary to create Cubes on the data to make it meaningful and consumable. There are cases when retrieving the data from cube takes lots of the time. Let us assume that your cube is returning you data very quickly. Suddenly on one day it is returning the data very slowly. What are the three things will you to diagnose this. After diagnose what you will do to resolve performance issue. Participate in my question over here I required BI Expert Jason Thomas to help with few hints to blog readers. He is one of the leading SSAS expert and writes a complicated subject in simple words. If queries were executing properly before but now take a long time to return the data, it means that there has been a change in the environment in which it is running. Some possible changes are listed below:-  1) Data factors:- Compare the data size then and now. Increase in data can result in different execution times. Poorly written queries as well as poor design will not start showing issues till the data grows. How to find it out? (Ans : SQL Server profiler and Perfmon Counters can be used for identifying the issues and performance  tuning the MDX queries)  2) Internal Factors:- Is some slow MDX query / multiple mdx queries running at the same time, which was not running when you had tested it before? Is there any locking happening due to proactive caching or processing operations? Are the measure group caches being cleared by processing operations? (Ans : Again, profiler and perfmon counters will help in finding it out. Load testing can be done using AS Performance Workbench (http://asperfwb.codeplex.com/) by running multiple queries at once)  3) External factors:- Is some other application competing for the same resources?  HINT : Read “Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services” (http://sqlcat.com/whitepapers/archive/2007/12/16/identifying-and-resolving-mdx-query-performance-bottlenecks-in-sql-server-2005-analysis-services.aspx) Well, these are great tips. Now win big prizes by participate in my question over here. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQLbits London 2012 - Demos

  - by Adam Machanic

  Thanks to everyone who attended my sessions last Friday and Saturday at SQLbits! It was great to meet many new people, not to mention spending some time exploring one of my favorite cities, London. Attached are the demos for each of the two talks I delivered: Query Tuning Mastery: The Art of and Science of Manhandling Parallelism As a database developer, your job boils down to one word: performance. And in today's multi-core-driven world, query performance is very much determined by how well you're...(read more)

  Read the article

• Is there an equivalent of jsqlparser but for SPARQL instead of SQL?

  - by Programmer

  I'm trying to use Java to construct a SPARQL query, and then send it off to a remote database. However, I'm new to both Java and SPARQL, so I was wondering if anyone could explain how to do this, rather than just posting a link. I heard there is a tool called jsqlparser for the same task, except that it's for a SQL to SPARQL conversion using Java. Conversion nor parser won't be necessary, just a method for constructing a query and querying the database provided by the user.

  Read the article

• Why isn't my query using any indices when I use a subquery?

  - by sfussenegger

  I have the following tables (removed columns that aren't used for my examples): CREATE TABLE `person` ( `id` int(11) NOT NULL, `name` varchar(1024) NOT NULL, `sortname` varchar(1024) NOT NULL, PRIMARY KEY (`id`), KEY `sortname` (`sortname`(255)), KEY `name` (`name`(255)) ); CREATE TABLE `personalias` ( `id` int(11) NOT NULL, `person` int(11) NOT NULL, `name` varchar(1024) NOT NULL, PRIMARY KEY (`id`), KEY `person` (`person`), KEY `name` (`name`(255)) ) Currently, I'm using this query which works just fine: select p.* from person p where name = 'John Mayer' or sortname = 'John Mayer'; mysql> explain select p.* from person p where name = 'John Mayer' or sortname = 'John Mayer'; +----+-------------+-------+-------------+---------------+---------------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+---------------+---------------+---------+------+------+----------------------------------------------+ | 1 | SIMPLE | p | index_merge | name,sortname | name,sortname | 767,767 | NULL | 3 | Using sort_union(name,sortname); Using where | +----+-------------+-------+-------------+---------------+---------------+---------+------+------+----------------------------------------------+ 1 row in set (0.00 sec) Now I'd like to extend this query to also consider aliases. First, I've tried using a join: select p.* from person p join personalias a where p.name = 'John Mayer' or p.sortname = 'John Mayer' or a.name = 'John Mayer'; mysql> explain select p.* from person p join personalias a on p.id = a.person where p.name = 'John Mayer' or p.sortname = 'John Mayer' or a.name = 'John Mayer'; +----+-------------+-------+--------+-----------------------+---------+---------+-------------------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-----------------------+---------+---------+-------------------+-------+-----------------+ | 1 | SIMPLE | a | ALL | ref,name | NULL | NULL | NULL | 87401 | Using temporary | | 1 | SIMPLE | p | eq_ref | PRIMARY,name,sortname | PRIMARY | 4 | musicbrainz.a.ref | 1 | Using where | +----+-------------+-------+--------+-----------------------+---------+---------+-------------------+-------+-----------------+ 2 rows in set (0.00 sec) This looks bad: no index, 87401 rows, using temporary. Using temporary only appears when I use distinct, but as an alias might be the same as the name, I can't really get rid of it. Next, I've tried to replace the join with a subquery: select p.* from person p where p.name = 'John Mayer' or p.sortname = 'John Mayer' or p.id in (select person from personalias a where a.name = 'John Mayer'); mysql> explain select p.* from person p where p.name = 'John Mayer' or p.sortname = 'John Mayer' or p.id in (select id from personalias a where a.name = 'John Mayer'); +----+--------------------+-------+----------------+------------------+--------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+----------------+------------------+--------+---------+------+--------+-------------+ | 1 | PRIMARY | p | ALL | name,sortname | NULL | NULL | NULL | 540309 | Using where | | 2 | DEPENDENT SUBQUERY | a | index_subquery | person,name | person | 4 | func | 1 | Using where | +----+--------------------+-------+----------------+------------------+--------+---------+------+--------+-------------+ 2 rows in set (0.00 sec) Again, this looks pretty bad: no index, 540309 rows. Interestingly, both queries (select p.* from person ... or p.id in (4711,12345) and select id from personalias a where a.name = 'John Mayer') work extremely well. Why doesn't MySQL use any indices for both of my queries? What else could I do? Currently, it looks best to fetch person.ids for aliases and add them statically as an in(...) to the second query. There certainly has to be another way to do this with a single query. I'm currently out of ideas though. Could I somehow force MySQL into using another (better) query plan?

  Read the article

• How to add Spatial Solr to a Solrnet query

  - by Flo

  Hi, I am running Solr on my windows machine using jetty. I have downloaded the Spatial Solr Plugin which I finally managed to get up and running. I am also using Solrnet to query against Solr from my asp.net mvc project. Now, adding data into my index seems to work fine and the SpatialTierUpdateProcessorFactory does work as well. The problem is: How do I add the spatial query to my normal query using the Solrnet library. I have tried adding it using the "ExtraParams" parameter but that didn't work very well. Here is an example of me trying to combine the spatial query with a data range query. The date range query works fine without the spatial query attached to it: new SolrQuery("{!spatial lat=51.5224 long=-2.6257 radius=10000 unit=km calc=arc threadCount=2}") && new SolrQuery(MyCustomQuery.Query) && new SolrQuery(DateRangeQuery); which results in the following query against Solr: (({!spatial lat=51.5224 long=-2.6257 radius=100 unit=km calc=arc threadCount=2} AND *:*) AND _date:[2010-05-07T13:13:37Z TO 2011-05-07T13:13:37Z]) And the error message I get back is: The remote server returned an error: (400) Bad Request. SEVERE: org.apache.solr.common.SolrException: org.apache.lucene.queryParser.Pars eException: Cannot parse '(({!spatial lat=51.5224 lng=-2.6257 radius=10000 unit= km calc=arc threadCount=2} AND *:*) AND _date:[2010-05-07T13:09:49Z TO 2011-05-0 7T13:09:49Z])': Encountered " <RANGEEX_GOOP> "lng=-2.6257 "" at line 1, column 2 4. Was expecting: "}" ... Now, the thing is if I use the Solr Web Admin page and execute the following query against it, everything works fine. {!spatial lat=50.8371 long=4.35536 radius=100 calc=arc unit=km threadcount=2}text:London What is the best/correct way to call the spatial function using SolrNet. Is the best way to somehow add that bit of the query manually to the query string and is so how? Any help is much appreciated!

  Read the article

• Codeigniter image manipulation class rotates image during resize

  - by someoneinomaha

  I'm using Codeigniter's image manipulation library to re-size an uploaded image to three sizes, small, normal and large. The re-sizing is working great. However, if I'm resizing a vertical image, the library is rotating the image so it's horizontal. These are the config settings I have in place: $this->resize_config['image_library'] = 'gd2'; $this->resize_config['source_image'] = $this->file_data['full_path']; $this->resize_config['maintain_ratio'] = TRUE; // These change based on the type (small, normal, large) $this->resize_config['new_image'] = './uploads/large/'.$this->new_file_name.'.jpg'; $this->resize_config['width'] = 432; $this->resize_config['height'] = 288; I'm not setting the master_dim property because the default it set to auto, which is what I want. My assumption is that the library would take a vertical image, see that the height is greater than the width and translate the height/width config appropriately so the image remains vertical. What is happening (apparently) is that the library is rotating the image when it is vertical and sizing it per the configuration. This is the code in place I have to do the actual re-sizing: log_message('debug', 'attempting '.$size.' photo resize'); $this->CI->load->library('image_lib'); $this->CI->image_lib->initialize($this->resize_config); if ($this->CI->image_lib->resize()) { $return_value = TRUE; log_message('debug', $size.' photo resize successful'); } else { $this->errors[] = $this->CI->image_lib->display_errors(); log_message('debug', $size.' photo resize failed'); } $this->CI->image_lib->clear(); return $return_value;

  Read the article

• Python pixel manipulation library

  - by silinter

  So I'm going through the beginning stages of producing a game in Python, and I'm looking for a library that is able to manipulate pixels and blit them relatively fast. My first thought was pygame, as it deals in pure 2D surfaces, but it only allows pixel access through pygame.get_at(), pygame.set_at() and pygame.get_buffer(), all of which lock the surface each time they're called, making them slow to use. I can also use the PixelArray and surfarray classes, but they are locked for the duration of their lifetimes, and the only way to blit them to a surface is to either copy the pixels to a new surface, or use surfarray.blit_array, which requires creating a subsurface of the screen and blitting it to that, if the array is smaller than the screen (if it's bigger I can just use a slice of the array, which is no problem). I don't have much experience with PyOpenGL or Pyglet, but I'm wondering if there is a faster library for doing pixel manipulation in, or if there is a faster method, in Pygame, for doing pixel manupilation. I did some work with SDL and OpenGL in C, and I do like the idea of adding vertex/fragment shaders to my program. My program will chiefly be dealing in loading images and writing/reading to/from surfaces.

  Read the article

• MySQL Query That Can Pull the Data I am Seeking?

  - by Amy

  On the project I am working on, I am stuck with the table structure from Hades. Two things to keep in mind: I can't change the table structure right now. I'm stuck with it for the time being. The queries are dynamically generated and not hard coded. So, while I am asking for a query that can pull this data, what I am really working toward is an algorithm that will generate the query I need. Hopefully, I can explain the problem without making your eyes glaze over and your brain implode. We have an instance table that looks (simplified) along these lines: Instances InstanceID active 1 Y 2 Y 3 Y 4 N 5 Y 6 Y Then, there are multiple data tables along these lines: Table1 InstanceID field1 reference_field2 1 John 5 2 Sally NULL 3 Fred 6 4 Joe NULL Table2 InstanceID field3 5 1 6 1 Table3 InstanceID fieldID field4 5 1 Howard 5 2 James 6 2 Betty Please note that reference_field2 in Table1 contains a reference to another instance. Field3 in Table2 is a bit more complicated. It contains a fieldID for Table 3. What I need is a query that will get me a list as follows: InstanceID field1 field4 1 John Howard 2 Sally 3 Fred The problem is, in the query I currently have, I do not get Fred because there is no entry in Table3 for fieldID 1 and InstanceID 6. So, the very best list I have been able to get thus far is InstanceID field1 field4 1 John Howard 2 Sally In essence, if there is an entry in Table1 for Field 2, and there is not an entry in Table 3 that has the instanceID contained in field2 and the field ID contained in field3, I don't get the data from field1. I have looked at joins till I'm blue in the face, and I can't see a way to handle the case when table3 has no entry.

  Read the article

• Modify request querystring paramaters to build a new link without resorting to string manipulation

  - by Andrew M

  Hi All, I want to dynamically populate a link with the URI of the current request, but set one specific query string parameter. All other querystring paramaters (if there are any) should be left untouched. And I don't know in advance what they might be. Eg, imagine I want to build a link back to the current page, but with the querystring parameter "valueOfInterest" always set to be "wibble" (I'm doing this from the code-behind of an aspx page, .Net 3.5 in C# FWIW). Eg, a request for either of these two: /somepage.aspx /somepage.aspx?valueOfInterest=sausages would become: /somepage.aspx?valueOfInterest=wibble And most importantly (perhaps) a request for: /somepage.aspx?boring=something /somepage.aspx?boring=something&valueOfInterest=sausages would preserve the boring params to become: /somepage.aspx?boring=something&valueOfInterest=wibble Caveats: I'd like to avoid string manipulation if there's something more elegant in asp.net that is more robust. However if there isn't something more elegant, so be it. I've done (a little) homework: I found a blog post which suggested copying the request into a local HttpRequest object, but that still has a read-only collection for the querystring params. I've also had a look at using a URI object, but that doesn't seem to have a querystring

  Read the article

• How do I get PHP variables from this MySQL query?

  - by CT

  I am working on an Asset Database problem using PHP / MySQL. In this script I would like to search my assets by an asset id and have it return all related fields. First I query the database asset table and find the asset's type. Then depending on the type I run 1 of 3 queries. <?php //make database connect mysql_connect("localhost", "asset_db", "asset_db") or die(mysql_error()); mysql_select_db("asset_db") or die(mysql_error()); //get type of asset $type = mysql_query(" SELECT asset.type From asset WHERE asset.id = 93120 ") or die(mysql_error()); switch ($type){ case "Server": //do some stuff that involves a mysql query mysql_query(" SELECT asset.id ,asset.company ,asset.location ,asset.purchase_date ,asset.purchase_order ,asset.value ,asset.type ,asset.notes ,server.manufacturer ,server.model ,server.serial_number ,server.esc ,server.user ,server.prev_user ,server.warranty FROM asset LEFT JOIN server ON server.id = asset.id WHERE asset.id = 93120 "); break; case "Laptop": //do some stuff that involves a mysql query mysql_query(" SELECT asset.id ,asset.company ,asset.location ,asset.purchase_date ,asset.purchase_order ,asset.value ,asset.type ,asset.notes ,laptop.manufacturer ,laptop.model ,laptop.serial_number ,laptop.esc ,laptop.user ,laptop.prev_user ,laptop.warranty FROM asset LEFT JOIN laptop ON laptop.id = asset.id WHERE asset.id = 93120 "); break; case "Desktop": //do some stuff that involves a mysql query mysql_query(" SELECT asset.id ,asset.company ,asset.location ,asset.purchase_date ,asset.purchase_order ,asset.value ,asset.type ,asset.notes ,desktop.manufacturer ,desktop.model ,desktop.serial_number ,desktop.esc ,desktop.user ,desktop.prev_user ,desktop.warranty FROM asset LEFT JOIN desktop ON desktop.id = asset.id WHERE asset.id = 93120 "); break; } ?> So far I am able to get asset.type into $type. How would I go about getting the rest of the variables (laptop.model to $model, asset.notes to $notes and so on)? Thank you.

  Read the article

• Insert array to mysql database php

  - by ganjan

  Hi. I want to add an array to my db. I have set up a function that checks if a value in the db (ex. health and money) has changed. If the value is diffrent from the original I add the new value to the $db array. Like this $db['money'] = $money_input + $money_db;. function modify_user_info($conn, $money_input, $health_input){ (...) if ($result = $conn->query($query)) { while ($user = $result->fetch_assoc()) { $money_db = $user["money"]; $health_db = $user["health"]; } $result->close(); //lag array til db med kolonnene som skal fylles ut som keys i array if ($user["money"] != $money_input){ $db['money'] = $money_input + $money_db; //0 - 20 if (!preg_match("/^[[0-9]{0,20}$/i", $db['money'])){ echo "error"; return false; } } if ($user["health"] != $health_input){ $db['health'] = $health_input + $health_db; //0 - 4 if (!preg_match("/^[[0-9]{0,4}$/i", $db['health'])){ echo "error"; return false; } if (($db['health'] < 1) or ($db['health'] > 1000)) { echo "error"; return false; } } The keys in $db represent colums in my database. Now I want to make a function that takes the keys in the array $db and insert them in the db. Something like this ? $query = "INSERT INTO `main_log` ( `id` , "; foreach(range(0, x) as $num) { $query .= array_key.", "; } $query = substr($query, 0, -3); $query .= " VALUES ('', "; foreach(range(0, x) as $num) { $query .= array_value.", "; } $query = substr($query, 0, -3); $query .= ")";

  Read the article

• PHP - print content from file after manipulation

  - by ludicco

  Hello there, I'm struggling trying to read a php file inside a php and do some manipulation..after that have the content as a string, but when I try to output that with echo or print all the php tags are literally included on the file. so here is my code: function compilePage($page,$path){ $contents = array(); $menu = getMenuFor($page); $file = file_get_contents($path); array_push($contents,$menu); array_push($contents,$file); return implode("\n",$contents); } and this will return a string like <div id="content> <h2>Here is my title</h2> <p><? echo "my body text"; ?></p> </div> but this will print exactly the content above not compiling the php on it. So, how can I render this "compilePage" making sure it returns a compiled php result and not just a plain text? Thanks in advance

  Read the article

• sql statement question. Need to query 3 tables in one go!

  - by Stefan

  Hey there, I have an sql database. In this database is 3 tables I need to query. The first table has all the item info called item and the other two tables has data for votes and comments called userComment and the third for votes called userItem I currently have a function which uses this sql query to get the latest more popular (in terms of both votes and comments): $sql = "SELECT itemID, COUNT(*) AS cnt FROM ( SELECT `itemID` FROM `userItem` WHERE FROM_UNIXTIME( `time` ) >= NOW() - INTERVAL 1 DAY UNION ALL SELECT `itemID` FROM `userComment` WHERE FROM_UNIXTIME( `time` ) >= NOW() - INTERVAL 1 DAY AND `itemID` > 0 ) q GROUP BY `itemID` ORDER BY cnt DESC"; I know how to change this for either by votes alone or comments.... HOWEVER - I need to query the database to only return the itemID's of the ones which have specific conditions in only the item table these are WHERE categoryID = 'xx' AND typeID = 'xx' If the sql ninja could please help me on this one? Do I have to first return the results from the above query and the for each in the array fetched then check each against the item table and see if it fits the conditions to build a new array - or is that overkill? Thanks, Stefan

  Read the article

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