image field where clause
I have sql table (20million records)with a image field A. I need to set up a where clause like ---- select * from tablea where A(image type) is not null. that query takes forever. How do I optimize that query? Thanks,
|
0 |
|
Mecn
6/10/2010 9:30:53 PM
sqlserver.programming
1873 articles. 
0 followers.
1 Replies
906 Views
Mecn (mecn@yahoo.com) writes: > I have sql table (20million records)with a image field A. I need to set > up a where clause like ---- select * from tablea where A(image type) is > not null. > that query takes forever. > How do I optimize that query? About how many rows in the table do you expect to have a non-NULL value in A? If it 16 million rows, and the image columns are big, it will take some time to return all that data. And your client may choke before the query completes. If only a small amount of rows have a value in A, you could add a computed column: has_image AS CASE WHEN A IS NULL THEN 0 ELSE 1 END and then index that column, and run your query over that column. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|
|
0 |
|
Erland
6/10/2010 10:01:38 PM
Similar Artilces:
field chooser: deleting fields
I created new fields in several non-user-field columns (such as Contact fields). But now I can't delete them and I have too many with the same name that don't work. The "delete" button for fields in the Field Chooser is only working with the User Fields. I have scoured the web and Help resources. Nothing. Does anyone know how to delete fields from Field Chooser? Do the fields actually "live" somewhere on the computer? -- Beryl Did you create them in a view or on a custom form? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlo...
Refreshing Form with Multiple Forms and Fields
I have a main screen form that allows entry to start from that location. It has four subforms and some other fields that are picked from other locations to filter the record. My challenge is it does not Refresh right away. I have tried putting Refresh and Repaint etc in the main form code but it still doesn't refresh. I put a Refresh button on it and that works but it's slow. Can anyone tell me what would be a more efficient method to get this screen to refresh right away. Thank you, ...
year conversion in date fields eg 1900 to 2000
I moved a existing database from foxpro to access 2000. The dates in foxpro had a 2 digit year and when brought in to access it converted them to 1900 (eg. 11/12/00 to 11/12/1900 instead of 11/12/2000). Is there a way in access to convert (replace) only the year of vaious dates to another year. If this is not possible is there a way to prevent this while converting? thanks alex An update query can work for this. Something like this: UPDATE TableName SET [DateFieldName] = DateAdd("yyyy", 100, [DateFieldName]) WHERE Year([DateFieldName]) = 1900; -- Ken Snell <MS...
URLDownloadToFile and image size
I am using URLDownloadToFile to download images from url. I only want download images with a certain size. Is there a way to get the image size(width, height) during the download process(before download complete)? Thanks, qk ...
protect Publisher fields/tables
How to protect fields, text & tables in Publisher in similar way it is done in Excel? You can't, maybe Word would work for you. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Loopy" <Loopy@discussions.microsoft.com> wrote in message news:928267E9-911A-4EB2-84B3-450D99B576CD@microsoft.com... > How to protect fields, text & tables in Publisher in similar way it is done > in Excel? ...
Multiple Records 1 Field
I need to create a report where I consolidate multiple records from 1 table and join them to 1 record in another. The problem is, I need them to all be joined into 1 record. tblStudents (Name, LocID, Grade) - Single record per student tblMods-Math (ModID, LocID, Mod) - Multiple mods for 1 kiddos is normal Finished product needs to be SomeQuery (Name, LocID, Grade, Mod1, Mod2, Mod3, Mod4, etc...) How do I do that? I tried creating a crosstab query, but that didn't work out. -- Thanks As Always Rip Rank the Mod values, per LocID. One possible solution is: SELECT a.locID, a.mod, COU...
Populate empty fields with a number
I have a a column which has 3 possibilites, 1,2 or 3. I have already populated fields with either a 1 or 2 based on another query. I wish to populate the remainder of fields which have no content with 3. I have tried to write the query as UPDATE dbase SET dbase.[field] = 3 WHERE dbase.[field] <> 1 AND <> 2. But this doesn't work, and have tried UPDATE dbase SET dbase.[field] = 3 WHERE dbase.[field] = "", and this doesn't work either. Data type mismatch occurs on last query. Any help would be great? Cheers -- Paul Wilson Paul, You might try: UPDATE dbas...
CC field not visible in public folder
Greetings, I have set up many mail-enabled Public Folders, to which our domain users foward messages and also to which oustide e-mail addresses send messages. If an internal user (w/ an AD account) CC's the public folder, the message appears in the public folder with the important header elements (from:, to:, CC:, etc). However, if an external email address sends a message, it appears in the public folder with ONLY the From field. Viewers of this public message can not see who else was CC'd. View > Header yields no help, nor is there a Tools > Options > Header opti...
PDOI: Image display.
I hope to be able to display an image. I've looked at and tried to apply sample code from a number of sources, including Sam's _Teach Yourself Visual C++ in 21 Days_, an MFC Bible, Jeff Proise's MFC book, etc. I would like to set up an array as follows data[0] = 0; data[1] = 0; data[2] = 255; to draw a red square, rect, or triangle. Once I see I can control pixel values, invoke the CBitmap class and control the display of the pixels, I'll worry about the code that reads the image file to be displayed. Can someone point me in the right direction. I also started a basic M...
Field Population
I am familiar with using an Access database and populating fields in Word, can you do the same thing in Excel. -- S Sharon Not really sure what you mean but is Data>Form... what you are looking for? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Sharon" <Sharon@discussions.microsoft.com> wrote in message news:85EE58F8-4BD5-4AD4-B40C-3B9792FDC8EF@microsoft.com... >I am familiar with using an Access database and populating fields in Word, > can you do the same thing in Excel. > -- >...
Split a street address field apart.
How do I split a street address field apart? Field will have 2-3-4-5 digits, followed by a space then a series of alphas. i.e. 837 Third Avenue -or- 6 Street West. In a column of these address fields, wish to create two fields: the 2-5 numericals only in one, and the remaining alphas in another. Not VBA-oriented, please, what might be the best, and simplest for my mind, way to achieve this? Using Office 2007. Kind Regards, Wayne Dengel Try this: A1: (address text....eg 837 Third Avenue) The number B1: =LEFT(A1,FIND(" ",A1)-1) The street C1: =TRIM(MID(A1,FIND(&...
Increase distribution reference field to at least 50 characters.
My client uses the distribution reference field which appear to be very long but only allows 30 characters. They would great appreciate having the field be at least 50 characters long. -- Thanks a lot, Julie London Technology Management Concepts (TMC) ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "...
Add image over other images
Hey guys, Basically I have two pictures: A map (quite a large picture) A symbol (quite small and to be placed over the map at specified points) Anyone have any idea how I could go about this? Thanks in advance for any help! Am 05.05.2010 05:51, schrieb bourkeyo: > Basically I have two pictures: > > A map (quite a large picture) > A symbol (quite small and to be placed over the map at specified points) > > Anyone have any idea how I could go about this? Take a look at the following classes/methods: 'Bitmap': Constructor and 'Save' m...
Change field type to lookup column
Can you (and if so, how?) change the data type of a populated field to lookup from a table without losing all the previously input data? -- savannah Do not change the field in the table but in your form use a list box. -- KARL DEWEY Build a little - Test a little "savannah" wrote: > Can you (and if so, how?) change the data type of a populated field to lookup > from a table without losing all the previously input data? > -- > savannah Thanks Karl. However, forms are not being used at this time. Data is entered directly on the form. I do understand that this f...
How to use custom field in email view
Hi all, Using Outlook 2003 in English I've added a some custom text fields (eg. "Request ID") to the current view (inbox folder). I was hoping to be able to type a value into this new text field for each email in the inbox, so I could easily sort and reference the messages. I see the empty column in the view on screen, but how can I enter a value in that column? Double-click opens the message and it's not in the message properties or options... Any ideas? Thank you in advance, James YOu can do that if you set the view for in-cell editing. It's on the = Other S...
Link a form field in a Word document to populate an excel spreads
i'm trying to populate a cell in an excel spreadsheet with a form field in a word document. is this possible? ...
'To' Field
Hi All, Is there a way of turning off the 'To' field in Outlook 2003 on new messages. I have a client who needs to make sure all outgoing messages are sent Bcc so they want to hide the 'To' field to stop any mistakes. TIA Terry ...
Add new field to grid
I have added the Customer Ship to Address to the RM Customer Inquiry grid. I need to create VBA code to populate this field. In a grid window, where do I open the data base and at what point do I read the table to populate this field. Thank you Amy Hi Amy Using the Window_AfterOpen event, you will need to use RetrieveGlobals to obtain the login credentials, then ADO to open a connection to the SQL server. You close the connection on the Window_AfterClose event. Then on the Before_LinePopulate event on the Grid form, you perform the actual SQL query. I have an example you can have ...
Update field (multiple criteria and updates)
I would like to update 2 fields with the following Where [item description] like "*BASIC*" update [item description] = "BASIC" and [item] = "HITS" Where [item description] like "*FARE*" update [item description] = "FARE" and [item] = "HITS" Where [item description] like "*SEARCH*" update [item description] = "SEARCH" and [item] = "HITS" I typically use design view to set up queries and I can only do one of the three changes in this view. Is there are way to write this in sql to do all thr...
Hidden Field in Datasheet view for a table
Hello All, when I go to Table Design, there is a column named Field 1 (it is text). When I switch to Datasheet View, it becomes hidden. Any ideas on how to solve the problem? Thanks in advance Sam Berry "Hidden" as in zero-width, or "hidden" as in "off-screen"? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is ...
Logical tests between Word form text fields
I have a Drop-Down form field with "Yes" and "No". I want to place the value 4 in a Text form field in the same document if the user selects "Yes" in the Drop-Down field. I set the Text form field to type "Calculation" but can not figure out the formula to use that doesn't return a Syntax error or "Undefined Bookmark, Yes". While I have Word 2007, I need to create this form for users with 2003 and so am using Legacy form fields. Hi jstern, Instead of using a calculation formfield, I'd suggest an ordinary formula field,...
How to Enter Same Text in 1000s of Fields
I have a an Access 2003 db of 11,000+ records. Each record has a field named Product Type. The field is now blank and I would like to enter the phrase audio books into each field. Can I do this with a query? How? You could use an update query with a SQL view like: UPDATE [an Access 2003 db of 11,000+ records] SET [Product Type]="audio books"; -- Duane Hookom Microsoft Access MVP "lbohen" wrote: > I have a an Access 2003 db of 11,000+ records. Each record has a field named > Product Type. The field is now blank and I would like to enter the phrase > audio...
Formview update fields
I have a formview that uses an ObjectDataSource. If I place a field on the formview that is not part of the update statement I get an error. I thought that the fields that are included in the update statement are those declared in the update parameters collection. If I have the EXACT same fields on a gridview the update is successful. Also is there any way of tracing the database execution so I can view all the statements going to the database? Thanks...Larry ...
Limit Unbound Field to One Character
Hi, How can I limit the length of an unbound text box to one character in length? I only want a user to enter one character. Thanks, Anthony -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200708/1 See: Unbound text box: limiting entry length at: http://allenbrowne.com/ser-34.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "biganthony via AccessMonster.com" <u31673@uwe> wrote in...
Accessing a new field
In Access 2000, I have added, via code, a new field into a table. The field name is "Date_" & Date Every time I run the program, no more than once a day, I add this new field (column) to the table. How do I access this field, in code, so I can enter data via rst.Edit and rst.Update and rst.MoveNext? The data I am entering has already been gathered previously in code. Thanks -- Shell Shell - The format for referencing a field in a recordset is rst!fieldname. Your actual fieldname will depend on your date settings, so look at the table to check the...
I created new fields in several non-user-field columns (such as Contact fields). But now I can't delete them and I have too many with the same name that don't work. The "delete" button for fields in the Field Chooser is only working with the User Fields. I have scoured the web and Help resources. Nothing. Does anyone know how to delete fields from Field Chooser? Do the fields actually "live" somewhere on the computer? -- Beryl Did you create them in a view or on a custom form? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlo...
Refreshing Form with Multiple Forms and Fields
I have a main screen form that allows entry to start from that location. It has four subforms and some other fields that are picked from other locations to filter the record. My challenge is it does not Refresh right away. I have tried putting Refresh and Repaint etc in the main form code but it still doesn't refresh. I put a Refresh button on it and that works but it's slow. Can anyone tell me what would be a more efficient method to get this screen to refresh right away. Thank you, ...
year conversion in date fields eg 1900 to 2000
I moved a existing database from foxpro to access 2000. The dates in foxpro had a 2 digit year and when brought in to access it converted them to 1900 (eg. 11/12/00 to 11/12/1900 instead of 11/12/2000). Is there a way in access to convert (replace) only the year of vaious dates to another year. If this is not possible is there a way to prevent this while converting? thanks alex An update query can work for this. Something like this: UPDATE TableName SET [DateFieldName] = DateAdd("yyyy", 100, [DateFieldName]) WHERE Year([DateFieldName]) = 1900; -- Ken Snell <MS...
URLDownloadToFile and image size
I am using URLDownloadToFile to download images from url. I only want download images with a certain size. Is there a way to get the image size(width, height) during the download process(before download complete)? Thanks, qk ...
protect Publisher fields/tables
How to protect fields, text & tables in Publisher in similar way it is done in Excel? You can't, maybe Word would work for you. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Loopy" <Loopy@discussions.microsoft.com> wrote in message news:928267E9-911A-4EB2-84B3-450D99B576CD@microsoft.com... > How to protect fields, text & tables in Publisher in similar way it is done > in Excel? ...
Multiple Records 1 Field
I need to create a report where I consolidate multiple records from 1 table and join them to 1 record in another. The problem is, I need them to all be joined into 1 record. tblStudents (Name, LocID, Grade) - Single record per student tblMods-Math (ModID, LocID, Mod) - Multiple mods for 1 kiddos is normal Finished product needs to be SomeQuery (Name, LocID, Grade, Mod1, Mod2, Mod3, Mod4, etc...) How do I do that? I tried creating a crosstab query, but that didn't work out. -- Thanks As Always Rip Rank the Mod values, per LocID. One possible solution is: SELECT a.locID, a.mod, COU...
Populate empty fields with a number
I have a a column which has 3 possibilites, 1,2 or 3. I have already populated fields with either a 1 or 2 based on another query. I wish to populate the remainder of fields which have no content with 3. I have tried to write the query as UPDATE dbase SET dbase.[field] = 3 WHERE dbase.[field] <> 1 AND <> 2. But this doesn't work, and have tried UPDATE dbase SET dbase.[field] = 3 WHERE dbase.[field] = "", and this doesn't work either. Data type mismatch occurs on last query. Any help would be great? Cheers -- Paul Wilson Paul, You might try: UPDATE dbas...
CC field not visible in public folder
Greetings, I have set up many mail-enabled Public Folders, to which our domain users foward messages and also to which oustide e-mail addresses send messages. If an internal user (w/ an AD account) CC's the public folder, the message appears in the public folder with the important header elements (from:, to:, CC:, etc). However, if an external email address sends a message, it appears in the public folder with ONLY the From field. Viewers of this public message can not see who else was CC'd. View > Header yields no help, nor is there a Tools > Options > Header opti...
PDOI: Image display.
I hope to be able to display an image. I've looked at and tried to apply sample code from a number of sources, including Sam's _Teach Yourself Visual C++ in 21 Days_, an MFC Bible, Jeff Proise's MFC book, etc. I would like to set up an array as follows data[0] = 0; data[1] = 0; data[2] = 255; to draw a red square, rect, or triangle. Once I see I can control pixel values, invoke the CBitmap class and control the display of the pixels, I'll worry about the code that reads the image file to be displayed. Can someone point me in the right direction. I also started a basic M...
Field Population
I am familiar with using an Access database and populating fields in Word, can you do the same thing in Excel. -- S Sharon Not really sure what you mean but is Data>Form... what you are looking for? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Sharon" <Sharon@discussions.microsoft.com> wrote in message news:85EE58F8-4BD5-4AD4-B40C-3B9792FDC8EF@microsoft.com... >I am familiar with using an Access database and populating fields in Word, > can you do the same thing in Excel. > -- >...
Split a street address field apart.
How do I split a street address field apart? Field will have 2-3-4-5 digits, followed by a space then a series of alphas. i.e. 837 Third Avenue -or- 6 Street West. In a column of these address fields, wish to create two fields: the 2-5 numericals only in one, and the remaining alphas in another. Not VBA-oriented, please, what might be the best, and simplest for my mind, way to achieve this? Using Office 2007. Kind Regards, Wayne Dengel Try this: A1: (address text....eg 837 Third Avenue) The number B1: =LEFT(A1,FIND(" ",A1)-1) The street C1: =TRIM(MID(A1,FIND(&...
Increase distribution reference field to at least 50 characters.
My client uses the distribution reference field which appear to be very long but only allows 30 characters. They would great appreciate having the field be at least 50 characters long. -- Thanks a lot, Julie London Technology Management Concepts (TMC) ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "...
Add image over other images
Hey guys, Basically I have two pictures: A map (quite a large picture) A symbol (quite small and to be placed over the map at specified points) Anyone have any idea how I could go about this? Thanks in advance for any help! Am 05.05.2010 05:51, schrieb bourkeyo: > Basically I have two pictures: > > A map (quite a large picture) > A symbol (quite small and to be placed over the map at specified points) > > Anyone have any idea how I could go about this? Take a look at the following classes/methods: 'Bitmap': Constructor and 'Save' m...
Change field type to lookup column
Can you (and if so, how?) change the data type of a populated field to lookup from a table without losing all the previously input data? -- savannah Do not change the field in the table but in your form use a list box. -- KARL DEWEY Build a little - Test a little "savannah" wrote: > Can you (and if so, how?) change the data type of a populated field to lookup > from a table without losing all the previously input data? > -- > savannah Thanks Karl. However, forms are not being used at this time. Data is entered directly on the form. I do understand that this f...
How to use custom field in email view
Hi all, Using Outlook 2003 in English I've added a some custom text fields (eg. "Request ID") to the current view (inbox folder). I was hoping to be able to type a value into this new text field for each email in the inbox, so I could easily sort and reference the messages. I see the empty column in the view on screen, but how can I enter a value in that column? Double-click opens the message and it's not in the message properties or options... Any ideas? Thank you in advance, James YOu can do that if you set the view for in-cell editing. It's on the = Other S...
Link a form field in a Word document to populate an excel spreads
i'm trying to populate a cell in an excel spreadsheet with a form field in a word document. is this possible? ...
'To' Field
Hi All, Is there a way of turning off the 'To' field in Outlook 2003 on new messages. I have a client who needs to make sure all outgoing messages are sent Bcc so they want to hide the 'To' field to stop any mistakes. TIA Terry ...
Add new field to grid
I have added the Customer Ship to Address to the RM Customer Inquiry grid. I need to create VBA code to populate this field. In a grid window, where do I open the data base and at what point do I read the table to populate this field. Thank you Amy Hi Amy Using the Window_AfterOpen event, you will need to use RetrieveGlobals to obtain the login credentials, then ADO to open a connection to the SQL server. You close the connection on the Window_AfterClose event. Then on the Before_LinePopulate event on the Grid form, you perform the actual SQL query. I have an example you can have ...
Update field (multiple criteria and updates)
I would like to update 2 fields with the following Where [item description] like "*BASIC*" update [item description] = "BASIC" and [item] = "HITS" Where [item description] like "*FARE*" update [item description] = "FARE" and [item] = "HITS" Where [item description] like "*SEARCH*" update [item description] = "SEARCH" and [item] = "HITS" I typically use design view to set up queries and I can only do one of the three changes in this view. Is there are way to write this in sql to do all thr...
Hidden Field in Datasheet view for a table
Hello All, when I go to Table Design, there is a column named Field 1 (it is text). When I switch to Datasheet View, it becomes hidden. Any ideas on how to solve the problem? Thanks in advance Sam Berry "Hidden" as in zero-width, or "hidden" as in "off-screen"? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is ...
Logical tests between Word form text fields
I have a Drop-Down form field with "Yes" and "No". I want to place the value 4 in a Text form field in the same document if the user selects "Yes" in the Drop-Down field. I set the Text form field to type "Calculation" but can not figure out the formula to use that doesn't return a Syntax error or "Undefined Bookmark, Yes". While I have Word 2007, I need to create this form for users with 2003 and so am using Legacy form fields. Hi jstern, Instead of using a calculation formfield, I'd suggest an ordinary formula field,...
How to Enter Same Text in 1000s of Fields
I have a an Access 2003 db of 11,000+ records. Each record has a field named Product Type. The field is now blank and I would like to enter the phrase audio books into each field. Can I do this with a query? How? You could use an update query with a SQL view like: UPDATE [an Access 2003 db of 11,000+ records] SET [Product Type]="audio books"; -- Duane Hookom Microsoft Access MVP "lbohen" wrote: > I have a an Access 2003 db of 11,000+ records. Each record has a field named > Product Type. The field is now blank and I would like to enter the phrase > audio...
Formview update fields
I have a formview that uses an ObjectDataSource. If I place a field on the formview that is not part of the update statement I get an error. I thought that the fields that are included in the update statement are those declared in the update parameters collection. If I have the EXACT same fields on a gridview the update is successful. Also is there any way of tracing the database execution so I can view all the statements going to the database? Thanks...Larry ...
Limit Unbound Field to One Character
Hi, How can I limit the length of an unbound text box to one character in length? I only want a user to enter one character. Thanks, Anthony -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200708/1 See: Unbound text box: limiting entry length at: http://allenbrowne.com/ser-34.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "biganthony via AccessMonster.com" <u31673@uwe> wrote in...
Accessing a new field
In Access 2000, I have added, via code, a new field into a table. The field name is "Date_" & Date Every time I run the program, no more than once a day, I add this new field (column) to the table. How do I access this field, in code, so I can enter data via rst.Edit and rst.Update and rst.MoveNext? The data I am entering has already been gathered previously in code. Thanks -- Shell Shell - The format for referencing a field in a recordset is rst!fieldname. Your actual fieldname will depend on your date settings, so look at the table to check the...